| Bug #52312 | lost Handler_read_last status variable | ||
|---|---|---|---|
| Submitted: | 23 Mar 2010 18:15 | Modified: | 20 Nov 2010 22:45 |
| Reporter: | Gleb Shchepa | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | any, 5.1.46-bzr | OS: | Any |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[23 Mar 2010 18:23]
Valeriy Kravchuk
Verified just as described: mysql> drop table t1; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1 (a INT, INDEX (a)); Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(); Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> FLUSH STATUS; Query OK, 0 rows affected (0.00 sec) mysql> SELECT a FROM t1 ORDER BY a DESC LIMIT 1; +------+ | a | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'HANDLER_READ%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 6 rows in set (0.00 sec) mysql> SELECT a FROM t1 ORDER BY a LIMIT 1; +------+ | a | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'HANDLER_READ%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 1 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 6 rows in set (0.00 sec) Looks funny, indeed. I'd consider this a missing/incomplete feature.
[25 Aug 2010 18:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116793 3192 Marc Alff 2010-08-25 Bug#52312 lost Handler_read_last status variable Before this fix, the ha_read_last_count status variable was defined and updated internally, for never exposed as a system variable. This fix exposes the system variable as "Handler_read_last", for completness of the Handler_read_* system variables interface. Adjusted tests results accordingly.
[26 Aug 2010 17:04]
Christopher Powers
Reviewed
[26 Aug 2010 20:34]
Marc ALFF
Patch queued into: - mysql-5.5-bugfixing - mysql-trunk-bugfixing - mysql-next-mr-bugfixing
[27 Aug 2010 6:37]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116950 3256 Tor Didriksen 2010-08-27 Bug#52312 lost Handler_read_last status variable Post-push fix: adjust more test results.
[30 Aug 2010 8:30]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)
[30 Aug 2010 8:33]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[30 Aug 2010 8:36]
Bugs System
Pushed into mysql-5.5 5.5.7-m3 (revid:alik@sun.com-20100830082727-5ac4czrxl61w9wle) (version source revid:alik@sun.com-20100830082727-5ac4czrxl61w9wle) (merge vers: 5.5.7-m3) (pib:21)
[31 Aug 2010 14:09]
Marc ALFF
Hi Paul Yes, this is status variable. The semantic is: The number of times the *last* entry in an index was read. This relates to Handler_read_first: http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Handler_read_f...
[31 Aug 2010 14:53]
Paul DuBois
Noted in 5.5.7, 5.6.1 changelogs. A new status variable, Handler_read_last, displays the number of requests to read the last key in an index. With ORDER BY, the server will issue a first-key request followed by several next-key requests, whereas with With ORDER BY DESC, the server will issue a last-key request followed by several previous-key requests.
[13 Nov 2010 16:12]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
Description: MySQL has a plenty of Handler_read_% status variables, but there is no Handler_read_last: mysql> CREATE TABLE t1 (a INT, INDEX (a)); mysql> INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(); mysql> FLUSH STATUS; mysql> SELECT a FROM t1 ORDER BY a DESC LIMIT 1; +------+ | a | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'HANDLER_READ%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 6 rows in set (0.00 sec) # it looks confusing if like we did no index access at all... # ... but: mysql> EXPLAIN SELECT a FROM t1 ORDER BY a DESC LIMIT 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: NULL key: a key_len: 5 ref: NULL rows: 1 Extra: Using index 1 row in set (0.00 sec) # let try LIMIT 2: mysql> FLUSH STATUS; mysql> SELECT a FROM t1 ORDER BY a DESC LIMIT 2; +------+ | a | +------+ | NULL | | NULL | +------+ 2 rows in set (0.00 sec) mysql> SHOW STATUS LIKE 'HANDLER_READ%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 1 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 6 rows in set (0.00 sec) # huh-huh, it looks like we did only 1 read for 2 records! # at the same time ascending order looks as expected: mysql> FLUSH STATUS; mysql> SELECT a FROM t1 ORDER BY a LIMIT 1; +------+ | a | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'HANDLER_READ%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 1 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ mysql> EXPLAIN SELECT a FROM t1 ORDER BY a LIMIT 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: NULL key: a key_len: 5 ref: NULL rows: 1 Extra: Using index 1 row in set (0.00 sec) How to repeat: CREATE TABLE t1 (a INT, INDEX (a)); INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(); FLUSH STATUS; SELECT a FROM t1 ORDER BY a DESC LIMIT 1; SHOW STATUS LIKE 'HANDLER_READ%'; or just: SHOW STATUS LIKE 'HANDLER_READ%'; Suggested fix: Add "Handler_read_last" status variable.