Mysql – Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don’t work

indexinnodbMySQLperformance

I have a Django web application that stores data in a MySQL InnoDB database. There is a particular page that is accessed a lot on the django admin, and the query is taking a long time (~20 seconds). Since it's the Django internals, the query cannot be changed.

There are 3 tables, A, B, and C. The query looks like:

SELECT * 
FROM A 
   INNER JOIN B ON (A.b_id = B.foo) 
   INNER JOIN C ON (B.foo = C.id) 
ORDER BY A.id DESC 
LIMIT 100

A simple join-3-tables together.

The id fields are primary keys and have indexes. A.b_id, B.foo both have their own indexes.

However the query plan looks wrong and says it's not using any keys on B (but it is using the keys for the other joins). From reading lots of MySQL performance stuff it should in theory be using indexes, since it's various const joins that can 'fall through'. It says it has to scan all ~1,200 rows of B.

The weird thing is that I OPTIMIZEed each one on my local machine and re-ran the queries (with SQL_NO_CACHE) and it was much faster, 0.02sec vs. the original 20sec. EXPLAIN on the same query gave a different, and much more sensible result, showing that it can use an index on each one, and that it doesn't have to scan the whole lot. A co-worker ran OPTIMIZE for each one on a testing machine with approximately the same data (which was recently recreated from a loaded dump file) and it also showed a speed increase, and a sensible-explain.

So we ran that on the live system… and it didn't change anything (the speed nor explain). I recreated my MySQL database (DROPed the database and reloaded from a dump), and now the OPTIMIZE doesn't change anything (i.e. ~20sec run time, bad query plan).

Why does this happen? How can I get MySQL to use the correct indexes and get back my 0.02s query time? This blog post ( http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/ ) implies that OPTIMIZE only really optimizes the primary key (not that b_id, foo are not primary indexes). How can I "rebuild secondary index"? I tried doing ALTER TABLE A ENGINE=InnoDB (and the same for B & C) and there was no change.

Actually the more I look at this, the more it seems to be a MySQL query plan fail. It's doing the wrong query plan and not using indexes that it could use. After doing an OPTIMIZE TABLE etc., it is sometimes able to use the correct indexes. When there's a bad query plan like this, it can be sorta randomly selected, that's why (I think) different machines have different results.

Best Answer

OPTIMIZE rebuilds the table. This (for InnoDB) squeezes out some of the fragmentation and wasted space. This is unlikely to make a noticeable difference in any query.

Also, OPTIMIZE does an ANALYZE. This has a chance of changing the statistics, thereby leading to a different (better or worse) EXPLAIN plan.

Since ANALYZE is much faster (on InnoDB) than OPTIMIZE, just do the ANALYZE.

Various non-ANALYZE actions cause an ANALYZE to be done.

ANALYZE randomly probes the BTrees, gathering stats. Sometimes the resulting stats are poor. There is effectively no way to prevent this from happening. Several partial hacks have been created over the years; 5.6.7 gets close to eliminating this problem with ANALYZE. Here's one of them: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages

Notice another contrast

The table count is 4636881
The EXPLAIN plan for the first query traversed 4000010 indexed_column keys. No need to read the last 636871 keys.
The EXPLAIN plan for the second query traversed 4636881 rows ordered by indexed_column. For every row picking up the non-indexed column out of the table, the indexed column (already sorted by the index) is looked up and comes along for the ride. The tmp table gets ordered by the indexed column, and mysqld then dismisses the first 4000000 rows, leaving 10 rows. All that interaction between table and index just for 10 rows is the bottleneck.

COMMON THINGS

In both instances, the query specfies the number of rows to traverse. Since the number of rows in the table is 4636881, we should readily expect a full scan. The contrast becomes apparent when the MySQL Query Optimizer decides where to perform the full scan.

The first query is referencing an indexed column only in the SELECT list and the WHERE clause. The MySQL Query Optimizer chooses to perform a full index scan with no need to contact the table since everything needed is in the index.
The second query is referencing an indexed column in the WHERE clause. However, it must reach out the table to retrieve the corresponding non-indexed column. The MySQL Query Optmizer was tipped off by the query that it must not use the index becasue of the number of rows it was expected to read. As a rule of thumb for any RDBMS, if more than 5% of a table has to be read to fulfill a query, the MySQL Query Optimizer will just throw the index 'under the bus' and do a full table scan.

Doing the math, here is what MySQL Query optimizer computes:

5% of 4636881 is 231844
The second query is commanded to read 4000000 rows, which is way higher than 231844
MySQL Query Optimizer realizes there would be too much interaction between table (because of the non-indexed column) and index (because of the indexed column) to get the needed data. It decides to read the table only (because both the indexed and non-indexed columns are residing together in the table) rather than bounce back and forth between them.

In my honest opinion, with the row count of the table, the present indexes the table has, and the number of rows prescribed by the query, the MySQL Query Optimizer made the correct decision.

RECOMMENDATION

Create this index

ALTER TABLE `table` ADD INDEX mynewndx (indexed_column,some_other_column);

and your second query will never touch the table again going forward. The MySQL Query Optimizer will behave quite differently when it sees this new index.

Mysql – Query results in a strange explain output

Since you didn't provide a full explain it's hard to tell what exactly is slowing your query down. However I can try to explain what the EXPLAIN output you provided means.

According to MySQL EXPLAIN Join types the index join type means that the query scans the whole index "for each combination of rows from the previous tables". In other words it is doing a full index scan, which might be not good.

Using Index in Extra column of EXPLAIN, documented in MySQL EXPLAIN Join types, means that the table data (i.e. the MYD file in case of MyISAM tables) is not read at all, because the columns contained in the index include all data needed by the query.

The NULL in the key column does mean that the index is not used for looking up rows.

This means that the query is really walking the whole index tree, though I doubt that reading 15k rows from index might be the real problem with your query.

Best Answer

Related Solutions

Mysql – Why does MySQL choose this execution plan

Notice another contrast

COMMON THINGS

RECOMMENDATION

Mysql – Query results in a strange explain output

Related Question