I have a MySQL 5.7.20, I use SQLYog Community as my MySQL client and I have a Tomcat Server running a Java application connecting with MySQL Connector 5.1.37.

My query is bad and is being rewritten, the question is not on how to improve that particular query. It loads the content of the table by chunck of 2000 lines, but does a JOIN on itself to also load multiple versions of a line, identified by a number, under the main version of the line.

FROM myTable AS t, myTable AS t2
WHERE t2.lineNumber = t.lineNumber
AND t2.version = 0
AND t.executionNumber='87710'
AND t2.executionNumber='87710'
AND t.lineNumber IN (24000,24001,24002,24003,[...],25996,25997,25998,25999)
AND t2.lineNumber IN (24000,24001,24002,24003,[...],25996,25997,25998,25999)
AND (t.version <> 0 OR ( 1  )) AND  1
ORDER BY t.lineNumber, t.version > 0, t.recordDate DESC;

In my case the total size of the table is a little over 160000 lines, so the load takes 81 executions of the query. This takes a random amount of time ranging from a few seconds to over an hour. What I notice is if I recreate the table from scratch and call the web server, each execution of the query takes about 43 secondes. If I run an EXPLAIN FOR CONNECTION using the MySQL client, I get a really bad execution plan.

1    SIMPLE    t    NULL    ref    executionNumber,lineNumber    executionNumber    4    const    80752    100.00    Using where; Using temporary; Using filesort
1    SIMPLE    t2    NULL    ALL    executionNumber,lineNumber    NULL    NULL    NULL    161504    5.00    Using where; Using join buffer (Block Nested Loop)

On the other hand, if I run a "regular" EXPLAIN and copy paste the query, I get a better execution plan.

1    SIMPLE    t2    NULL    range    executionNumber,lineNumber    lineNumber    4    NULL    2000    5.00    Using index condition; Using where; Using temporary; Using filesort
1    SIMPLE    t    NULL    ref    executionNumber,lineNumber    lineNumber    4    labo.t2.lineNumber    1    50.00    Using where

The weird part is, after running that second EXPLAIN, the performance on the web server improves and the Java call that was running for many minutes ends in seconds. I have reproduce this many times, waiting an arbitraty number of times between the start of the Java call and the run of the EXPLAIN. The query coming from the Java server always improve from the first query following my EXPLAIN.

So my question is, how can the execution of an EXPLAIN query changes the performances of another query ?

My best guess is that the MySQL query optimizer keeps offering a bad execution plan from its cache until I run the EXPLAIN, that forces it to compute a new execution plan, but I couldn't find any doc confirming that theory. And it wouldn't explain why the query optimizer offers a bad execution plan to a Java webapp executing a query and a better execution plan to a MySQL client running and EXPLAIN on that same query.

  • I am not sure if it makes sense, but so far the most plausible answer is that the system attempts a first read when the table is empty, and there is no statistics to get a good execution plan, then fills the table and then attempts a second read without evaluating the new data. This guess is base on the fact that if I execute the same call later without recreating the table, the call is efficient. – user327961 Jan 22 '18 at 13:07
  • Does things change if you change the IN-expressions to BETWEEN ... AND? E.g., "t.lineNumber BETWEEN 24000 AND 25999". Could it be that you are running with different settings for session variable eq_range_index_dive_limit ? When you say "loads the content of the table", do you mean that you are inserting the data first before reading it, or does "load" mean "read"? There is no plan cache, so that can not be the explanation. – oysteing Jan 22 '18 at 13:33
  • Use JOIN .. ON to establish how the tables are related. (Actually a "self-join" in this case.)
  • Since you are building the WHERE clause, get rid of the spurious stuff about 1 at the end.
  • If version cannot be negative, use > 0 instead of <> 0; this might be easier to optimize.
  • Since you are JOINing on executionNumber and lineNumber, there is no need to repeat the filtering on them.

Something like this:

    FROM  myTable AS t,
    JOIN  myTable AS t2
      ON   t2.lineNumber = t.lineNumber
      AND  t2.executionNumber = t.executionNumber
    WHERE  t2.version = 0
      AND  t.version > 0
      AND  t2.executionNumber='87710'
      AND  t2.lineNumber IN (24000,24001,24002,24003,[...],25996, 25997,25998,25999 )
    ORDER BY  t.lineNumber, t.version > 0, t.recordDate DESC;

Now it is easier to see what INDEXes are needed. (Please provide SHOW CREATE TABLE; meanwhile, I will guess.)

I think myTable needs both of these "composite" indexes:

INDEX(version, executionNumber, lineNumber)
INDEX(executionNumber, lineNumber, version)

Then show us EXPLAIN SELECT .... I think t2 will be referenced first, using the first index. Then t will be reached into using the second. In both cases, the "Rows" should be much less than what you saw.

There will be a "filesort" and probably "using temporary". They are unavoidable due to the complexity of the ORDER BY.

I assume the table is ENGINE=InnoDB? Then I don't see how 5.7.xx would end up with any inconsistencies in EXPLAIN. Gone are the days of re-probing to get statistics at the 'wrong' time.

I'm pretty sure the Optimizer has no 'cache' for explain-plans. However, the plan may change from one day to the next, due to lots of inserts, etc.

  • I already showed the 2 EXPLAINS in my question. As I mentionned in the 2nd paragraph, the question is not about how to optimise this query but why there actually are inconsistencies in EXPLAIN. I would have thought that those days are gone too, but not always it seems. Yes, table is InnoDB. – user327961 Jan 22 '18 at 13:02
  • @user327961 - I meant to provide the EXPLAIN after adding the suggested indexes. – Rick James Jan 23 '18 at 2:38

It seems our application suffers from the followin bug since we upgraded to MySQL 5.7. https://bugs.mysql.com/bug.php?id=70629

An ANALYZE TABLE has been added immediatly after sections that create a new table, populate it relativly fast and use it immediatly after in a JOIN. While not clean, it solved the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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