Always In The Last Place You Look

I've been scratching my head trying to work out why the main query that drives Minx blogs is so inefficient.  I tweak indexes and I tweak selection criteria and I tweak sorting and nothing seems to improve the query time reported at the bottom of the page.  Now, normally it's only 20ms or so, but if you catch it after an update - a new post or comment - it blows out to 400ms or so because the query cache just got flushed.

So I tweaked and I twiddled and I fiddled and fretted, and I took the query and plugged in some parameters and ran it outside of Minx, and it turned out to be, in fact, reasonably quick.

Then I tried disabling the sidebar to see what effect that had on the total query time.

19ms cached; 28ms uncached.  Perfectly reasonable for selecting and sorting and joining multiple fairly complex (albeit small) subsets of 13,000 records.

It's those darn recent comments again!

Update: And now I remember why recent comments are slow.  When you list the recent comments, you're listing the last 20 (or whatever) comments in a folder.  But comments aren't in folders, they're in threads, so you have to look at the threads in the folder, and then find the most recent comments from among the comments in those threads.  Big slow join before the sort.

There's a straightforward way to optimise the common case - use either multiple queries or subqueries to restrict MySQL to only inspect the 20 most recently updated threads.  That gets messy for the general case, though, where you want page 17 of recent comments.

Need to spend a bit more time thinking about this one.

Update: Gargh!  MySQL 5.0.27 doesn't support LIMIT in subqueries anyway.

Update: Well, it doesn't if you're using IN to restrict the main search to a subset of threads selected by the subquery, but if you use the subquery to return the update datetime of the nth most recently updated thread and then restrict the search to threads updated since then, you're swimming in gravy.

...

Which would be pretty unpleasant, come to think of it.

Posted by: Pixy Misa at 06:13 AM | Comments (19) | Add Comment | Trackbacks (Suck)
Post contains 367 words, total size 2 kb.