Twice as many Innodb_rows_read with Rowid-ordered scan

[Philip Stoev]

Not repeatable in maria 5.2. The following query against the DBT-3 scale 1 set:

SELECT COUNT( * ) FROM lineitem
WHERE ( l_commitDATE >= '1998-12-22' AND l_commitDATE IN ( '1993-12-11' , '1994-12-09' )
OR l_suppkey = 10 );

Causes the Innodb_rows_read counter to increase by 1204 , which is twice the number of rows examined by the query.

In maria-5.2, the counter increases by 602

Explain:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
         type: range
possible_keys: i_l_suppkey,i_l_commitdate
          key: i_l_suppkey
      key_len: 5
          ref: NULL
         rows: 601
        Extra: Using where; Rowid-ordered scan
1 row in set (0.00 sec)

The table was Innodb, with innodb_stats_sample_pages = 128 and ANALYZE TABLE

[Sergey Petrunia (sergefp)]

The effect is repeatable on the latest 5.3: setting

@@optimizer_switch='mrr=on' causes "Rowid-ordered scan" to used, and twice as many Innodb_rows_read counter increments.

[Sergey Petrunia (sergefp)]

I cannot say it's a performance regression, though: on hot buffer pool, the query executes in 0.00 sec both with MRR and without.

[Sergey Petrunia (sergefp)]

A note about the WHERE clause:

( l_commitDATE >= '1998-12-22' AND l_commitDATE IN ( '1993-12-11' , '1994-12-09' )
OR l_suppkey = 10 )

the part with l_commitDATE is always FALSE, however, if we remove it, the query will use ref(const) and not range with MRR.

[Sergey Petrunia (sergefp)]

Actually, there is little sense to use MRR in this query, because range access is done over a single {l_suppkey=10} range, which covers all key components. This way, the records it reads will already be in rowid order.

We could have had special handling for this kind of range scans but we didn't because we assumed that they will be converted to ref(const), which does not use MRR.

[Sergey Petrunia (sergefp)]

As for greater counter increments, this is expected. Increment of Innodb_rows_read counter cannot be used to judge performance. For example, the increment will be the same irrespectively of whether the scan uses "Using index", while performance of a scan with "Using index" is typically better.

[Sergey Petrunia (sergefp)]

To sum up, we don't consider this a performance/etc bug. I've added a note into documentation:
http://kb.askmonty.org/en/multi-range-read-optimization#why-using-multi-range-read-can-cause-higher-values-in-status-variables.

We may need to re-work server status variables, though, because now their correct interpretation is counter-intuitive (more reads == better, why???)