performing mysqldump corrected query issue?

[Peter Sylvester]

Lately we've been experiencing an issue with a particular query that filters on one large table (2.5 million rows), but then joins against several smaller ones (several hundred rows). Normally when I test this query it runs in .047 seconds or less, so I don't think code is an issue here. Though I can provide it upon request. All joins are done over indexes, so joining shouldn't be an issue here.

Today our application had issues with this same query and timed out after waiting for 30 seconds for the query to return data. Sure enough, the query was in the slow query log, reporting a time of greater than 30 seconds to get the full result set. Please bare in mind the result set in this case was 0 records.

This seemed quite strange given the query normally runs in a fraction of a second, so I took the same code and ran it on our slower integration server and it finished in .05 seconds. I thought that maybe it was a large difference in data, so I performed a mysqldump of the db in question on the production server and restored it on the integration software then performed the test again. With the exact same data, the integration server ran it in .05 seconds, just as it had before.

So I went back to the production server and ran the query again. Despite there being no changes, the query ran in .03 seconds. No code changed, no significant data changed, the explain statement showed the exact same thing as it did earlier, but now instead of running in 30 seconds, it ran in .03. The ONLY thing that changed is that I ran a mysqldump of the db on the production server.

Right now the only things that I think could cause the issues is either table fragmentation (not sure how mysqldump would fix this) or potentially issues with loading a dependent subquery into a temporary table (not sure how mysqldump would fix this either).

I was wondering if anyone here might be able to explain this strange activity.

- Peter