Comment 1 for bug 602047

This bug is much more serious than it sounds -- it causes all single-row lookups to not get counted by userstats patch at all. This is because userstats is incrementing counters by "(number rows read) - 1" each time, and when only 1 row is read, then counter is not incremented. This makes userstats patch useless for identifying unused indexes because it does not count any "SELECT ... WHERE column = $value" queries that match only one row.

I have tested this in latest releases: 5.1.53-11.7, 5.1.53-12.4, and 5.1.54-12.5.
Problem does not affect MyISAM, only InnoDB/XtraDB.
Here are some examples demonstrating this. I have attached table create and insert statement at end of comment.

### start with innodb table and clean counters

mysql [localhost] {msandbox} (test) > select * from information_schema.index_statistics;
Empty set (0.00 sec)

### read single row
mysql [localhost] {msandbox} (test) > select id from test where data='abd';
+----+
| id |
+----+
| 3 |
+----+
1 row in set (0.00 sec)

### counter does not change
mysql [localhost] {msandbox} (test) > select * from information_schema.index_statistics;
Empty set (0.00 sec)

### switch to myisam, then read single row
mysql [localhost] {msandbox} (test) > alter table test engine=myisam;
Query OK, 10 rows affected (0.23 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql [localhost] {msandbox} (test) > select id from test where data='abd';
+----+
| id |
+----+
| 3 |
+----+
1 row in set (0.00 sec)

### counter increases by 1 as expected !
mysql [localhost] {msandbox} (test) > select * from information_schema.index_statistics;
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| test | test | data | 1 |
+--------------+------------+------------+-----------+
1 row in set (0.00 sec)

### read 2 rows now
mysql [localhost] {msandbox} (test) > select id from test where data='abc';
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

### and counter increases by 2, as expected!
mysql [localhost] {msandbox} (test) > select * from information_schema.index_statistics;
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| test | test | data | 3 |
+--------------+------------+------------+-----------+
1 row in set (0.00 sec)

### switch back to innodb
mysql [localhost] {msandbox} (test) > alter table test engine=innodb;
Query OK, 10 rows affected (0.31 sec)
Records: 10 Duplicates: 0 Warnings: 0

### read same 2 rows again
mysql [localhost] {msandbox} (test) > select id from test where data='abc';
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

### but now counter only increases by 1 :(
mysql [localhost] {msandbox} (test) > select * from information_schema.index_statistics;
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| test | test | data | 4 |
+--------------+------------+------------+-----------+
1 row in set (0.00 sec)

----------------

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(32) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `data` (`data`)
) ENGINE=InnoDB;

INSERT INTO `test` VALUES (1,'abc','2011-02-03 21:45:37'),(2,'abc','2011-02-03 21:45:37'),(3,'abd','2011-02-03 21:45:37'),(4,'acd','2011-02-03 21:45:37'),(5,'def','2011-02-03 21:45:37'),(6,'pqr','2011-02-03 21:45:37'),(7,'stu','2011-02-03 21:45:37'),(8,'vwx','2011-02-03 21:45:37'),(9,'yza','2011-02-03 21:45:37'),(10,'def','2011-02-03 21:45:37');