MySQL bug 69179 – INFORMATION_SCHEMA.PARTITIONS causes query plan changes
May 26, 2013
Posted by on
Shard-Query examines INFORMATION_SCHEMA.PARTITIONS to determine if a table is partitioned. When a table is partitioned, Shard-Query creates multiple background queries, each limited to a single partition. Unfortunately, it seems that examining INFORMATION_SCHEMA.PARTITIONS causes query plans to change after the view is accessed.
I have reported bug 69179 to
MySQL AB Oracle Corporation(old habits die hard).
Be careful: If you have automated tools (like schema management GUI tools) then make sure they don’t examine INFORMATION_SCHEMA.PARTITIONS or you may get bad plans until you analyze your tables or restart the database, even if using persistent stats.
I can only get the bug to happen when a WHERE clause is issued that limits access to a single partition. It may be that the per partition statistics get wiped out when the I_S table is accessed, but the per table stats remain unchanged.
In order to get stable and good query plans I implemented a workaround in my local Shard-Query branch. I replaced access to INFORMATION_SCHEMA.PARTITIONS with a copy of the table stored in shard_query.partitions. This ensures that the query plans don’t change between the cold run and the hot run. This bug affects MySQL 5.5.31, 5.6.11, TokuDB 5.5.30, mariadb-5.5.30-tokudb-7.0.1 and possibly others.