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.
© 2024, Oracle Corporation and/or its affiliates