Database Updates

Photo provided by Baqash

As some of you might have noticed, I took AQ down for a bit today to do a massive update of the code. You won't see any changes--at least you shouldn't see any changes--because these were all "under the hood" updates. As I mentioned before, the database has been having trouble executing some of the queries efficiently, and the problem I've been having is that the piece of code I used for creating queries wasn't really designed for nitty-gritty details like telling the database precisely the order that tables should be joined or how best to execute certain queries. In the past, the database was generally pretty good at figuring out that stuff on its own without much help from me. Now.... it needs a little extra help, but the code hadn't been designed for that sort of thing.

So this update helps fix that problem. I completely replaced the code that put together queries, and it has a lot of options I can throw at it to suggest better ways of joining tables together. I can use subqueries, which I couldn't do before. I can change the type of join and force two specific tables to link together. I can throw parenthesis into the queries where needed. I can force the database to use certain indexes or ignore others as needed. (Well, technically, the old code could do that particular trick as well, but not as slickly or reliably as the updated code.)

All-in-all, it's a much more flexible piece of code that will allow me to fine-tune super complex queries like never before.

However, one thing it doesn't actually do is fix any slow queries. For a couple of reasons, really, but mainly because running a slow query from the live site doesn't necessarily show up when I run it on my development machine or vice-versa. The live site has traffic from lots of people at once which can cause locks and issues that won't show up on my development machine. Not to mention that hundreds of people using the live site will generate queries that cause problems that I could never hope to think of trying on my test machine.

So this update is just a stepping stone to faster, better queries. Now that it's running on the live site, I can monitor for problem queries and take the actual query and figure out where it's having trouble and figure out how I can modify it to work better--and the underlying code can be modified as needed to tweak the queries.

 Anyhow.... long story short.... progress is being made, but I'm not done yet. 

This, however, was a massive update that changed the very fundamental process of creating database queries, and it undoubtedly could have broken stuff. Please be patient--I'll try to fix any problems that crop up as quickly as possible. 

Thanks for being so patient!