Description:
In 5.1.25-rc, a range scan on a partitioned table appears to result in all index entries
from the start of the range through to the end of the partition, being accessed. This is
visible as high Handler_read_next activity.
This does not appear to be internal conversion from range to full index scan, as leading
index entries are still skipped.
While the result set is correct, presumably this behavior could result in a lot of
unneeded index-scan-like load for short ranges near the start of a large partition.
Found this at the same time as bug #37327, but the duplicate rows there do not affect
this test case.
How to repeat:
Execute this in 5.1.25-rc:
select version();
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
KEY `c1` (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
KEY `c1` (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (c1) (PARTITION a
VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION b VALUES LESS THAN MAXVALUE ENGINE =
MyISAM) */;
INSERT INTO `t1` VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
INSERT INTO `t2` VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
explain partitions select c1 from t1 where (c1 > 2 and c1 < 5);
flush status;
select c1 from t1 where (c1 > 2 and c1 < 5);
show status like 'Handler_read_%';
explain partitions select c1 from t2 where (c1 > 2 and c1 < 5);
flush status;
select c1 from t2 where (c1 > 2 and c1 < 5);
show status like 'Handler_read_%';
explain partitions select c1 from t1 where (c1 > 12 and c1 < 15);
flush status;
select c1 from t1 where (c1 > 12 and c1 < 15);
show status like 'Handler_read_%';
explain partitions select c1 from t2 where (c1 > 12 and c1 < 15);
flush status;
select c1 from t2 where (c1 > 12 and c1 < 15);
show status like 'Handler_read_%';
Returns:
+-----------+
| version() |
+-----------+
| 5.1.25-rc |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 20 rows affected (0.00 sec)
Records: 20 Duplicates: 0 Warnings: 0
Query OK, 20 rows affected (0.00 sec)
Records: 20 Duplicates: 0 Warnings: 0
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | NULL | range | c1 | c1 | 5 | NULL |
2 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+------+
| c1 |
+------+
| 3 |
| 4 |
+------+
2 rows in set (0.00 sec)
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 2 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | t2 | a | range | c1 | c1 | 5 | NULL |
2 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+------+
| c1 |
+------+
| 3 |
| 4 |
+------+
2 rows in set (0.00 sec)
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 18 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | NULL | range | c1 | c1 | 5 | NULL |
2 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+------+
| c1 |
+------+
| 13 |
| 14 |
+------+
2 rows in set (0.00 sec)
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 2 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | t2 | a | range | c1 | c1 | 5 | NULL |
2 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+------+
| c1 |
+------+
| 13 |
| 14 |
+------+
2 rows in set (0.00 sec)
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 8 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)
Note how Handler_read_next is always 2 for the normal MyISAM table, while it increases
inversely proportional to the start of the range for the partitioned table.
Suggested fix:
Might be a 'feature' or range scan limitation for partitioning. Requires documentation
at least.
