mySQL Inconsistent Performance

Question

Hi,

I'm running a mySQL query that joins various tables of 500,000+ rows. Sometimes it takes a second, other times around 15 seconds! This is on my local machine. I have experienced similarly varied times before on other intensive queries, does anyone know why this is?

Thanks

Thanks for the replies - I am using appropriate indexes, inner and left joins and have a WHERE clause range of one week out of possible 2 year period of invoices. If I keep varying it (so presumably query results are not cached) and re-running, time varies a lot, even if no. of rows retrieved is similar. The server is not busy. A few scheduled queries every minute but not intensive, take around 200ms.

The explain plan shows that a table of around 2000 rows is always fully scanned. So maybe these rows are sometimes cached, or maybe indexes are cached - didnt know indexes could be cached. I will try again with caching turned off.

Editing again - query cache is in fact off, I'm using InnoDB so looks like increasing innodb_buffer_pool_size is way to go

Answer 1

Same query each time?

It's hard to tell, based on what you've posted. If we assume that the schema and data aren't changing, I'd guess that there's something else running on your machine when the queries are long that would explain the difference. It could be that the state of memory is different, so paging is going on; an anti-virus program is running; some other service has started. It's impossible to answer.

Answer 2

Try to do an

Optimize Table

That should help to refresh some data useful for the query planner.

You have not give us much information, if you're using MyISAM tables, it may be a matter of locks.

Are you using ANSI INNER JOINs? Little basic, but don't use "cross joins". Those are the joins with the comma, like

SELECT * FROM t1, t2 WHERE t1.id_t1=t2.id_t1

Last things you may want to try. Increase your buffers (innodb), your key_buffers (myisam), and some query cache buffers.

Answer 3

Here's some common reasons(bar your server simply being too busy)

• The slow query is hitting the harddrive. In the fast case the indexes and data are already cached in MySQL or the OS file cache.

• Retrieving the data gets locked by updates/inserts, for MyISAM tables the whole table gets locked whenever someone inserts/updates data in it in some cases.

• Table statistics are out of date and/or the wrong index gets selected. running analyze oroptimize on the table can help.

• You have the query cache enabled, fetching the result of a cached query is fast, fetching it if it's not in the cache might be slow. Try turning off the query cache to check if the query is always slow if its not fetched from the cache.

In any case, you should show the output of EXPLAIN on your queries to verify indexes are getting used properly - even if they're not, queries can be fast if everything is in ram but grinding to a halt if it needs to hit the hardddrive.