0

EXPLAIN SELECT ... shows the EXPLAIN for a query (without actually running it).

SHOW EXPLAIN FOR conn_id shows the EXPLAIN for a query that is currently running.

Is it possible to get the EXPLAIN for the last query that ran on a connection?

For example, if my application detected that a specific query took over 2 seconds to run for a user, I'd like to log the EXPLAIN or ANALYZE with the query plan of that query.

I'm not sure that re-running the query with EXPLAIN or ANALYZE will give me the exact query plan that was used on its previous run, as MySQL/MariaDB could potentially use another query plan on the re-run.

(I am aware of log_slow_verbosity=query_plan,explain, but I don't want to log this for every query that takes over 1 second - only this specific query)

Improve this question
3
  • "never runs" -- perhaps you mean "never finishes" or "has not yet finished after hours"?
    – Rick James
    Nov 4, 2022 at 2:32
  • "potentially use another query plan" -- I have always believed that, but I have not found proof of such, even after thousands of Explains.
    – Rick James
    Nov 4, 2022 at 2:33
  • "never runs" just means that MySQL/MariaDB don't actually run the query when you do EXPLAIN SELECT.
    – Nuno
    Nov 4, 2022 at 9:11

1 Answer 1

Reset to default
0

"potentially use another query plan" -- I have always believed that, but I have not found proof of such, even after thousands of Explains.

I have seen as many as 6 different EXPLAINs for a given 'digest' -- The variation came from different constants in the queries. I used to capture the slowlogs from a hundred servers and would probe them to get the Explains (before the existence of log_slow_verbosity).

You seem to have found all the ways there are to see an Explain.

One caution -- when there is a subquery, EXPLAIN may evaluate that subquery as part of its work. This could lead to the Explain taking much more than the milliseconds it normally take.

I have no qualms about leaving the slowlog on, even on busy production machines. (But with long_quer_time of at least 1.)

What version are you running? Note that newer versions have a way to limit statement execution time.

If you would like to discuss an particularly naughty query, start a new Question with the query, SHOW CREATE TABLE, and EXPLAIN SELECT ... Explain tells you what is is doing, but not what to change (eg indexing) to make it faster.

Improve this answer
5
  • Thanks. Yeah, I use SET STATEMENT max_statement_time=10 for this query, but 99.99% takes less than 0.5 seconds to run. Just when it takes over 1 second, the system logs that it took over 1 second, but I'd like more information than that. I guess the closest I have is to re-run the query with EXPLAIN, when that happens. I have MariaDB 10.8. I also raised MDEV-29944 in their JIRA.
    – Nuno
    Nov 4, 2022 at 9:09
  • @Nuno - Is it ENGINE=InnoDB? (MyISAM is more prone to having mysteriously long executions times.) Turn on the slowlog with log_slow_verbosity and long_query_time=1 until it happens again.
    – Rick James
    Nov 4, 2022 at 15:49
  • I use InnoDB, and I have a long_query_time, but I don't want it to be 1, as that would cause too much noise. Like I said, this specific query is 99.99% running in less than 0.5 seconds. I just want a query plan logged in the very rare cases where this takes longer than 1 second. But not for ALL other queries. Thank you for your help.
    – Nuno
    Nov 4, 2022 at 16:57
  • @Nuno - If you have lots of queries taking more than 1 second, set a higher limit; fix the worst; then lower it. I'm suspicious of any query taking more than 1 second.
    – Rick James
    Nov 4, 2022 at 19:32
  • I understand, but it really depends on the type/nature of the application, what's it used for, what data it queries and processes, the users that run queries, etc. And some queries are much more crucial than others. Anyway - thanks for your help!
    – Nuno
    Nov 4, 2022 at 19:43

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.