3

I have a really large, complex query I'm trying to optimise using MySQL EXPLAIN SELECT or EXPLAIN EXTENDED SELECT.

If I run it against the query, I'll see every table in the query is using Using where in the Extra column, which is great.

No data will be changed at all, I'll go off and make a cup of tea or something, come back and re-run EXPLAIN.

This time, just a few minutes later, only 20% of the tables are Using where, the primary table is now Using index; Using temporary; Using filesort, and my day becomes a nightmare trying to debug this.

I am aware that sometimes things like temporary tables and filesorts are more efficient than using where clauses and indexes. But not in the case of this database, which is 10GB in size, and creating temporary tables and filesorts kills the server completely.

Any ideas why this would be happening? Is there logic or reason behind such a thing?!

Jack
  • 9,615
  • 18
  • 72
  • 112

1 Answers1

0

You are using InnoDB, correct? You are using a version older than 5.6.6, correct?

You have encountered an interesting variant on InnoDB's lack of "persistent statistics". Several things used to trigger re-computing the statistics for InnoDB tables. And those statistics are used for deciding how to execute the query.

Probably your particular query was "on the fence" -- a slight change in some statistic would lead to a different query plan.

If you would like, we could dig deeper. But we need to see

  • SHOW CREATE TABLE
  • SHOW TABLE STATUS (for clues of table size)
  • EXPLAIN EXTENDED SELECT...
  • EXPLAIN FORMAT=JSON SELECT... (5.6.5 or later)

And we may be able to suggest ways to speed up the query.

Rick James
  • 135,179
  • 13
  • 127
  • 222