MYSQL slow queries in "slow queries log" - but same queries runs very fast manually

Question

as title states, in my database various queries appear in the slow queries log, but when I run them manually they run 10x faster.

for example a relatively simple select query with several order by params takes often up to 100 seconds in log(yes table is very big)... but when I run it myself on the same DB it takes 2 seconds or so.

I've examined the server's performance and there doesn't seem to be a particular slowdown or bottleneck at the time, nor are many queries taking long time at that period but just the one.

how can I begin to analyze such a problem?

thanks for the help

Answer 1

Your system may have been more busy when the offending query entered the slow log.

The slow log may indicate that indexes aren't being fully utilized if the rows_examined is larger than the result set. Therefore, the execution time should be considered a clue that a query has a problem, but the rows_examined will give you more of a definitive answer.

Query time is a poor performance measurement on a server with high load because any query may be slow on a busy server. You should use EXPLAIN and SHOW PROFILE to profile your queries and improve them so that they do not put too much load on your server.

Use SELECT SQL_NO_CACHE ... when profiling queries so the results are not stored in the query cache, otherwise subsequent executions of the same query may use the query cache, skewing any timing of the query.