I host a trivia room (well, two) on an IRC network, it gets a few visitors, and has a host of regulars and is a great room.
I don’t have much involvement in it, it’s just running there, my partner does more with it then I do.
The trivia room runs off a MySQL database (primarily due to a redesign decision to give it more uumph then flat files), and I was just looking in their on an ‘occassional’ view, and noticed that the answers were coming through quickly, but the messages that follow, that tell the user the score, etc, were taking seconds to come through, possibly frustrating players with the extended delay that follows (ie waiting on a new question).
Anyway, I was pondering whether it was the query causing the high load, or whether it was something external to that, or something else (when it comes to databases, the issues are either bad queries or bad design).
The query was an extended union all query, which I thought wasn’t going to do much benefit like that, so I changed the code base to instead execute single queries to see if that was the performance issue (its more work to union results together then just fetch more).
It didn’t work as planned and I was then thinking, wait, I wonder if this table has any indexes or keys. Turns out it doesn’t.
So, I added indexes on username, username and date, and date and id fields on the database.
Amazingly, after it took its time saving those, the performance when from slug, to super fast in seconds.
So, an index is important, as your database grows to millions of rows, you need to have an index for high speed data access (and it really does give you high speed data access).
How indexes work ? Essentially they are small files of data pointing to full row record positions, so that way when data is needed to be found, less scanning of the full table is needed (just go ask the index).
Setting them up depends on what you use in your question. I used username and date fields because they are predominantly what the where queries are based off, we ask where are all the fields with this username, or where are all the fields with this username and date.
It’s a lot quicker to fetch that way, because we now know all the positions with that information, so its just fetching, and then getting the data together into a recordset.
The server load should be better off now too due to that issue causing it to see MySQL slug down to a crawl to fetch points for a specific user.
In unrelated news, it seems like we might be stuck with our unstable ADSL2+ service, simply because the Optus DSL ports have restrictions on the profiles they allow to be set, I’m told a 1500/256k service is possible, as is a 8192/384 service, and finally, an ADSL2+ service with a limit of 20,000 / 820 is possible.
So, we are having drop outs primarily due to our attenuation being too low then the service can handle reliably, continuously, so, the fix is to put a profile somewhere in the middle on, something that delivers us around 8Mbit with 820kbps up.
Unfortunately, the Optus ADSL1 limited profile is 8192/384- exactly the crap I had from Telstra Wholesale, and the reason I moved away (faster upload speed, cheaper service, no STUPID artificial limits).
Anyway, I already know that the 8192/384 service was stable on my line, and free of drop outs, and if I was going to get the same on the Optus port, then we might as well as just go back to ADSL2+ mode and see what else can be done for the drop outs.
It seems however, we are stuck with them, they aren’t really much of a problem anyway, except if they happen during the day on a VoIP call. I don’t care if I’m asleep and it drops out for one minute and comes back, doesn’t really affect much.
I would rather a stable service, but, it seems, we can’t move address due to the 12 month contract on the DSL service, but also, it’s just recently emerging that the exchange is all out of ports (wow, they filled up fast).
So, hopefully, over the next 12 months, something happens to make our service stable (ie. technician fixes a fault, something gets done to fix the drop out situation if its crosstalk at the exchange, etc), or, we simply work around it, and see what alternatives pop up after 12 months (ie. Change address for stable service, or, change internet service if new technology emerges that provides equivilent benefits).
I wonder how many ports Optus do deploy in their roll outs compared to customers they have. It can’t be too many, because they do seem to fill up quickly.
Our ADSL1 contract wasn’t supposed to be up til February, I was pondering waiting the 2 months for it to finish, but concluded this situation might happen, and watching neighbouring Gosford, I was right, they filled fast, and no new ports have been added to cause Gosford to become available.
I can work with drop outs, they don’t affect us much at night anyway (though they do happen at 9pm as well).
I’ll see how the second line works out too, which is going to get a ADSL2+ modem eventually too!
Enjoy!