Tuesday, May 15, 2007

LibraryThing for Libraries gets MUCH faster

The Tag Browser in FAAAAST!
The top complaint about the LibraryThing for Libraries was the speed of the tag browser. We revisited it today, and made it MUCH faster. It's positively zippy!

It proved to me, yet again, that sometimes your seemingly perfect MySQL query—JOINS across five tables on primary keys and EXPLAINs like a dream—can be broken into two queries for an order-of-magnitude speed increase. If I were smarter, I'd understand this.

3 Comments:

Blogger Zac said...

It's unclear exactly what you mean by "broken into two queries", but there are usually two things that go on here.

One is that the query planner ('explain') is simply wrong about some of the expectations, and the real results don't match well to the plan. I don't know MySQL, but with PostgreSQL you would try using 'explain analyze' on a few examples to see what's happening. In this case, once you smartened up the planner your original query would perform better.

The other is that sometimes it's just "better" to muck around with the data at the application level instead of the db level (especially once the datasets get small enough to handle), i.e., a coarser and much faster query is indeed sometimes the best choice, no matter how tempting it is to "perfect" it.

5/15/2007 4:10 PM  
Blogger "As You Know" Bob said...

"If I were smarter, I'd understand this."


That pretty much sums up my entire life.

5/16/2007 1:23 AM  
Anonymous sunny said...

:-)

5/16/2007 10:00 AM  

Post a Comment

<< Home