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:
None 
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:15] Gleb Shchepa
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.
[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.