- MySQL doesn't explain INSERT, UPDATE and DELETE statements. As the optimizer for SELECT statements is different than that for the other statements, the query plan may be very different than expected.
- InnoDB uses sampling to provide index cardinality statistics. Different servers with the same data frequently have different stats when the distribution of rows to distinct key values is not uniform. This is a frequent cause for bad query plans and usually requires hints to force a join order.
- The contents of a temp table used by a query are not available to the DBA attempting to explain the query. It would help if I could run SHOW TEMP TABLE STATUS and SHOW TEMP TABLE INDEXES to understand the contents of all temp tables currently in use.
- A user may forget to collect statistics on a temp table prior to using it. I wish that stats were automatically collected in this case. They are not.
- The MySQL optimizer is very good at choosing the correct index as per-table selectivity estimates are extremely accurate. It has occasional problems choosing a good join order when there is skew. The problem is that the optimizer computes the join selectivity between tables based on the number of distinct number of values in the join columns (this is displayed by SHOW INDEXES for when the columns are indexes). This computation assumes that the number of rows per distinct value are uniformly distributed. The join selectivity is used to determine the join order. There is a page in the MySQL documentation that explains some of this but I cannot find it. We have many queries that force join orders to compensate for this. Forcing the index for a table is done much less frequently.
The plan for this query:
select * from foo, bar where foo.fk = bar.pkWill be:
foo:fk_index,bar:pk_indexThis can be extended to report on query progress by displaying the number of rows read from each table in the plan:
foo:fk_index:10,bar:pk_index:100This output can also be written to the slow query log.