FSTDT

Covered most of what I was doing last time, and 100% of the vote wanted to know more so here goes:

SQL Indexes, they're specifically ordered subsets of the data otherwise contained within the tables. You have a Numeric ID on a table 1-100, you most likely have an index that has nothing but that ID in it to reference that ID as fast as possible. In fact most IDs will have something like that because it makes the random crap developers need to do pretty quick, along with any simple functionality. These indexed columns are the columns your index runs on, when you're looking to make a decision based on certain fields having them indexed means you consult the index and check it in a pre-determined manner rather than checking every piece of data to see if it fits the criteria. The index does a fair bit of the work for you that way, it tells you which record you're looking for as fast as possible if setup correctly.

But that's not all you can do with them, like the old library catalogs you may be too young to remember, there can also be other information stored as an index entry. Summary, publisher, and a whole bunch of other information would be available when you found the index card in the catalog. SQL Indexes can do the same thing. See, in all but the most basic queries just finding the item you're looking for isn't enough, you also need to find information related to that item. Which means you can include columns which are not themselves indexed, but are immediately available from the index when an item is located.

That is what explains the difference in both sizes and effectiveness of the two indexes I was bandying about last post. The small index only contains the columns which are indexed, once the record is found the database engine then has to go consult the table and pull the remaining details. The large index contains ALL of the relevant columns, which happens to be all of the large columns in the comments table, and returns them immediately. As mentioned before, the immediate trade offs are size of the index and speed at which the intended query runs. The large index doubles the size of the table as it contains all the same information, but it runs twice as fast as the smaller index that is a very small fraction of the table size.

However there is one other concern that'll crop up real quick, maintaining the index on a live server. Like a library catalog, when entries are added or modified the index needs to be updated within the space allowed or you need to rebuild everything, including all columns stored within the index if they're part of the specifically indexed columns or not. Using the large index as an example this would mean that every time a comment is added space for the comment, author and various IDs would have to be allocated, plus any time a comment is modified an existing entry would have to be modified, possibly forcing it out of the current order and causing extra over head in pulling the index together. At that point you could rebuild or reorganize the index to effectiveness by rebuilding the index, but the data would be either unavailable or available at a severely reduced rate for the duration.

As it sits, I'm going with the smaller index because it'll hold together for far longer and can be rebuilt in seconds rather than around five minutes and it's still considerably faster than the existing query. Providing a consistent speed is often as important as anything else with databases, and it would do far better in that regard.

In theory I have time to poke at some of the other core code for the site this weekend, we'll see how that works out. If it works out I may be digging up a temp domain to try out a new host and beat the new code around before all that much longer.

Alright, have the basic mockup together. Now that I have it I'm not entirely sure why I thought I needed pictures, anyway here goes.

When you enter public admin you would be presented with a listing of quotes as shown below:
image
For every quote currently displayed within your public admin filters you'll have one of those.

Mostly what is shown on any given quote page today in a slightly crapper format, excepting the currently suggested tags and the Admin Panel. Well, the format will definitely be better when I spend more than 20 seconds on it, but the black boxes should give any aspiring designers an idea of my general div layout. More partitions can be added as needed, but that's my basics. The suggested tags do a quick show of any tags that have already been selected for the quote and when complete the admin panel will have one of those nice "I'm expandable push me" arrows in it's left corner.

When you push said not-yet-existent arrow the panel for the current quote will expand and display the following:
image
Giving all the current Admin details for the quote and supplying a method to enter any of information you need to enter.

Issues cover any general observations of a quote, be it corrections that need to be made, or reactions to the quote to explain your vote. In this case we have one person having had a slight overload and noted such. Any text entered into the issue will be saved when the admin changes are submitted, and will be displayed to anyone who examines the quote after that.

Our new big feature, Tags are the next major section. All tags are followed by their current vote count, any tag with a vote count of 1 or higher is selected automatically. If you wish to add a new tag all you need to do is select it, as I have with 'Scary' in honor of the guy in the comments of this quote who seems to think it involves a dead waitress, when the admin changes are submitted it will increase the tag's count from 0 to 1. The remaining tags would increment from 1 to 2. If you disagree with an existing tag, unselecting it will cause you to vote against that tag being included. If a tag is at 0 already, leaving it unchecked won't push it down any farther.

The last section is the quote approval selections, with effectively the same three options we have now along with tracking on how many time each has been given thus far. Approve, Reject(I like it more than Delete) and Abstain. Hitting the Submit Admin Changes button will pass this selection and any other admin panel data to the system through an asynchronous call. What this means is you get a little spinning wheel only on the one quote and can still play with the rest of the quotes you have displayed on the page and the quote will be completely removed from your public admin access. Once the call completes you will receive a notice that your changes have been submitted, and there shouldn't be any conflicts if multiple submissions are made in a short time frame.

What this gives us is the ability to look at all the quotes available, let people ignore certain quotes and continue out of order, not cause anyone to miss quotes if they check into public admin regularly and in the case of duplicates select the best one rather than the first one.

The potential problems would be voting 'irregularities' which I have a real simple plan to get around. Rather than incrementing the vote when a public admin vote is pressed, the entire vote will be recalculated using the most negative vote on record for all voters. Even if someone breaks the system their vote will still only count once.

The undecided mostly consist of the 'Public Admin Filters' I hinted at up above. Right now my plans for those would be having a day by day selection for public admin quotes. You pick a day and you have all of the quotes submitted that day are presented to you. But I'm open to more ideas on that, particularly any that would be useful for identifying duplicated quotes.

Blogs