Bug #82224 Rows read via InnoDB FTS index are not accounte Handler% status counters
Submitted: 14 Jul 2016 2:39 Modified: 14 Jul 2016 5:40
Reporter: Marcos Albe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.6, 5.6.31, 5.7.13 OS:Any
Assigned to: CPU Architecture:Any

[14 Jul 2016 2:39] Marcos Albe
Description:
Rows read via InnoDB FTS index are not accounte Handler% status counters, making it seemingly impossible to compare how many rows are accessed using one or other index.

How to repeat:
CREATE TABLE `my_audit` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `auditable_id` int(11) DEFAULT NULL,
  `auditable_type` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `user_type` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `audited_changes` text,
  `version` int(11) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `affected_user_id` int(11) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `remote_address` varchar(255) DEFAULT NULL,
  `association_id` int(11) DEFAULT NULL,
  `association_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_index` (`user_id`,`user_type`),
  KEY `index_audits_on_created_at` (`created_at`),
  KEY `index_audits_on_affected_user_id` (`affected_user_id`),
  KEY `auditable_index_with_created_at` (`auditable_id`,`auditable_type`,`created_at`),
  FULLTEXT KEY `audited` (`audited_changes`)
) ENGINE=InnoDB AUTO_INCREMENT=1387 DEFAULT CHARSET=latin1

mysql> select id from my_audit where audited_changes REGEXP 'territory_id:|first_name:|last_name:|role_id:|active:';
+----+
| id |
+----+
| 25 |
| 27 |
+----+
2 rows in set (0.02 sec)

mysql> show status like 'handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 2     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 201   |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.01 sec)

mysql> select id from my_audit where MATCH(audited_changes) AGAINST('territory_id:|first_name:|last_name:|role_id:|active:' IN BOOLEAN MODE);
+----+
| id |
+----+
| 25 |
| 27 |
+----+
2 rows in set (0.00 sec)

It does work (wonderfully) but it does not show accessed rows:

mysql> show status like 'handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 2     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
[14 Jul 2016 2:46] Marcos Albe
Seems to be exclusive of InnoDB:

msandbox@localhost (test) > alter table my_audit engine myisam;
Query OK, 273 rows affected (0.03 sec)
Records: 273  Duplicates: 0  Warnings: 0

msandbox@localhost (test) > flush status; select id from my_audit where MATCH(audited_changes) AGAINST('territory_id:|first_name:|last_name:|role_id:|active:' IN BOOLEAN MODE); show status like 'handler%';
Query OK, 0 rows affected (0.00 sec)

+-----+
| id  |
+-----+
| 256 |
| 258 |
+-----+
2 rows in set (0.01 sec)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_last          | 0     |
| Handler_read_next          | 3     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
[14 Jul 2016 5:40] Umesh Shastry
Hello Marcos Albe,

Thank you for the report.
Observed this with 5.6.31 build.

Thanks,
Umesh