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
Triage: Triaged: D3 (Medium)

[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] Valerii 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)