Wrong result from table_statistics

[Peter Zaitsev]

It looks like something is wrong with how table_statistics is counted
In this case we have provided 5 rows in result so at least 5 rows should have been locked at
and yet only 4 are shown.

mysql> set global userstat_running=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from y limit 5;
+------+
| i |
+------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+------+
5 rows in set (0.00 sec)

mysql> show table_statistics;
+--------------+------------+-----------+--------------+-------------------------+
| Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes |
+--------------+------------+-----------+--------------+-------------------------+
| test | y | 4 | 0 | 0 |
+--------------+------------+-----------+--------------+-------------------------+
1 row in set (0.00 sec)

[aeva black (tenbrae)]

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 |...

[Vadim Tkachenko (vadim-tk)]

provide test-case

[aeva black (tenbrae)]

My initial comment included CREATE and INSERT statements for the sample data that I used in the examples I posted. If that is not sufficient test case, I can upload additional samples, just let me know.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-414