As the EveKnows.com database grows ever larger, search times have been going up. Yesterday I tried some more SQL optimizations to alleviate this, and cut most search times in half. The problem seems to be disk access with large result sets, especially broad, single-word queries such as teen, babe, sex, etc. Finding the initial set of matching galleries isn’t much of a problem since the query uses fast indexing, but we then need to pull all of the details from, say 30,000 galleries, which is a problem. Some testing of the UNIX utilities sar and iostat reveal an incredible number of disk reads for these broad queries, and it’s only going to get worse as the database grows larger.
Until a few moments ago I wasn’t quite sure how to handle this, but I think I’ve just hit on a solution. Currently EveKnows makes two queries, one without a LIMIT command to figure out the total number of results, and then a second one with the LIMIT keyword to pull, say, the first 30 results (or 31-60 if the user is on the second page, etc.). I’m wondering if I adjust the first query to not pull the extra gallery data, if it might be able to work using only indexes. If that turns out to be true, our second, LIMITed query would only need to fetch full results for 30 rows at a time, which could be a substantial speed-up for popular search terms. Hrmm… I’ll try to implement this tonight and let you know the results! ;)
