Another database woes update....

Photo provided by Essex Explorers

Yes, I continue working on the problem. Yes, it still is a problem. Opening logbooks for premium members has definitely caused a lot of slow queries which I haven't been able to put in adequate hacks for, which is why logbooks are still closed to non-premium members. (I'm half tempted to close them again for premium members, but since it doesn't seem to actually be crashing the database, I'm leaving it open.)

Today, I reached another major milestone in my Next Big Update--which is primarily about making database queries faster and more efficient. I've updated the core subsystem that runs searches on my development machine, and it seems to work pretty well over all. Of course, just because it works well on my development machine doesn't guarantee the same on the live website. *knock on wood*

But this is a huge milestone, and completely rewrites how AQ queries the database when  you run a search. In some ways, the queries are simpler. In other ways, they're actually more complex. I use a lot more subqueries now, breaking big queries into smaller ones that are easier for the database to execute, but then the subqueries get merged together which adds complexity.

But most of you probably aren't interested in those sorts of nitty gritty details. Y'all want to know.... when will everything be back to normal?!

And... I'm still not sure. I just finished getting the core search queries done, but I still need to create unit tests and thoroughly kick the tires to make sure it all works as expected. And there are "mini-searches" scattered all over AQ that need to be updated all over the place. For example, a lot of the widgets on My Page are just the results of regular searches that can be performed manually, so all of those will need to be updated (and subsequently tested). Heck, even the logbooks are basically just specialized searches. All that code is connected.

So there's still a lot of work to be done... .but progress continues and at this point, I feel optimistic that I might be able to install a permanent fix and update AQ in... another month? Well, okay, a month might be optimistic, but theoretically possible. Two months might be more realistic.

Yeah, I know, like I said... there's still so much for me to test, fix, and make sure the update goes as smoothly as possible. I was reluctant to even give estimates before because I really couldn't even fathom how long this might take, but now that the core code has been updated and seems to work reasonably well, I feel like I have a much better insight into how long it'll take me to properly test and fix the new code and integrate it into the rest of AQ.

Limited logbooks available!

Photo provided by Angel Winks

I've been adding hacks to get the queries running faster and faster and.... they seem to be doing pretty well. So I've opened logbooks up for premium members. I'm still a bit worried how logbooks will handle, so for the time being, I'm limiting them to premium members and see how it goes. I'll hack those queries as needed, and eventually open the logbooks up for everyone again.

My database work isn't done, however. These updates to keep things running are ugly hacks, and I'm still working on a more long-term solution for the complex queries. I'm ripping out thousands of lines of old code and replacing them with fresh code, and it just takes time. When this next update is ready, it'll likely have bugs and problems that will need some ironing out, and without the ugly hacks in place, some of the queries might turn out to be unacceptably slow and I'll have to do more tweaks to the code.

So I'm not done with this issue by a long shot, but I also didn't want to keep the logbooks down any longer than was absolutely necessary. Thus... the hacks. I'll keep monitoring for problem queries and adding additional hacks as needed until the permanent fix is in place, but now that logbooks are back up, I feel less up against wall trying to get this all sorted out. Maybe I'll take a day off and relax. This snafu has been... well, not pleasant.

Sorry again for all the recent issues. Slow-loading pages, logbooks inaccessible, etc. But progress continues to be made! In the long run, it's a much needed upgrade for AQ, but in the short-term, it's been painful, and I'm sorry about that.

If you're wondering about the photos on the blog, there's a photo album on AQ called Letterboxing Photos for AQ. I've been grabbing them from there. Feel free to submit your own photos and maybe someday they'll be featured on AQ (or the AQ blog).