I have recently been running some fairly slow select statements, and what I notice is that the time reported at the end is typically much less than the time it actually ran. Is that to be expected - and what is the reason? I'm not looking to optimise my query, I just want to understand this better in general. My most recent statement ran for several hours with no locks and all alone on that server - however, at the end it showed:

7693 rows in set (17 min 36.08 sec)

When I EXPLAINed it, it showed that it would only do things like index lookups (no full table scans) with less than a few thousand rows examined at most. It just seems odd.

  • It is clear query execution time, and some overhead operations (data transfer, etc.) are not counted.
    – Akina
    Jan 14, 2020 at 10:35
  • Can you post the results of EXPLAIN EXTENDED as well as the output of SHOW CREATE TABLE foo\G for all tables foo that are touched by the query? Without more information, anything anybody tells you is just a guess! Also, read this (good post on using profiles in 5.5) and this (profile syntax). Please also give us the output of this (ALL) for your query. Are there any remote servers queries here? @Akina - from 17 mins to several hours seems too much just for networking issues?
    – Vérace
    Jan 14, 2020 at 10:39
  • @Vérace Not only networking issues. Any issue out of the query execution process. The most common reason of such difference is some caching lag or some third service lag (of some antiviral software, for example).
    – Akina
    Jan 14, 2020 at 10:45


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.