Running EXPLAIN in a SQL client improve performances on production server

Question

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.

SELECT t.*
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.

Answer 1

• 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:

SELECT  t.*
    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.

Answer 2

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.