This is the title of my first blog post on MySQL Performance Blog. It deals with a customer case where the customer was facing a peculiar problem where the rows column in the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the output of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows” estimation to pick and choose indexes and it could really be picking up a wrong index simply because of the wrong estimate. You...

I’ve used the OPTIMIZE command for a long time, in particular for disk space problems.

But I recently had a problem with this command and an auto-incremented table, you can reproduce this problem like that :

Create a table with an auto-incremented column :

mysql> use test
Database changed
mysql> create table test_optimize (id int(8) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)) engine=InnoDB;
Query OK, 0 rows affected (1.09 sec)

mysql> insert into test_optimize values (1),(2),(3),(4);
Query OK, 4 rows affected (0.28 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show table status like ‘test_optimize’\G
*************************** 1. row

  [Read more...]