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)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.086 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.