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.