| 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 (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| 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: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]
MySQL Verification Team
Hello Marcos Albe, Thank you for the report. Observed this with 5.6.31 build. Thanks, Umesh
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 | +----------------------------+-------+