| Bug #57599 | ORDER BY .. LIMIT stops using a key after removing partitioning on a table | ||
|---|---|---|---|
| Submitted: | 20 Oct 2010 11:59 | Modified: | 28 Oct 2010 18:31 |
| Reporter: | Elena Stepanova | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1, 5.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[28 Oct 2010 18:31]
Konstantin Osipov
This is not a bug. The optimizer is making the right choice. InnoDB stores a secondary index separately. Since you perform ORDER BY k, but SELECT all columns, InnoDB has to scan twice as many pages to retrieve the result if an index is used. A full table scan in your case is fully justified. Try adding more rows to the table, and you'll notice that the optimizer chooses a different plan. I agree that the effect of partitioning is not obvious. Perhaps partition pruning is causing the change in the plan.
[31 Oct 2010 12:52]
Elena Stepanova
Okay. I found the effect surprising mostly because if I create the table without partitioning from the start, the index is used, so it was not even partitioning itself, but the operation of removing partitioning that seemed to be causing the change in the plan; so I was wondering if the behavior was expected. However, it is indeed not a problem in itself as there are only few rows in the table.
Description: I have a table with an index on an int column, partitioned by this column. According to EXPLAIN or status variables, SELECT .. ORDER BY the index column LIMIT .. uses the index, but stops doing so after partitioning is removed (or if the number of partitions is reduced to 1). For the test case from 'How to repeat', the first EXPLAIN gives: id: 1 select_type: SIMPLE table: t_part type: index possible_keys: NULL key: k key_len: 4 ref: NULL rows: 1 Extra: and the second shows: id: 1 select_type: SIMPLE table: t_part type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 320 Extra: Using filesort If I add the actual SELECTs preceded by FLUSH STATUS, SHOW STATUS LIKE 'HANDLER_READ%' returns, correspondingly (on 5.5): +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 2 | | Handler_read_key | 4 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ and +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 1 | | Handler_read_key | 3 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 321 | +-----------------------+-------+ How to repeat: DROP TABLE IF EXISTS t_part; CREATE TABLE `t_part` ( `k` int(11) NOT NULL AUTO_INCREMENT, `f` int(11) DEFAULT NULL, KEY `k` (`k`) ) ENGINE=InnoDB PARTITION BY HASH (k) PARTITIONS 2; INSERT INTO t_part (f) VALUES (1),(2),(3),(4),(5); INSERT INTO t_part (f) SELECT f FROM t_part; INSERT INTO t_part (f) SELECT f FROM t_part; INSERT INTO t_part (f) SELECT f FROM t_part; INSERT INTO t_part (f) SELECT f FROM t_part; INSERT INTO t_part (f) SELECT f FROM t_part; INSERT INTO t_part (f) SELECT f FROM t_part; EXPLAIN SELECT * FROM t_part ORDER BY k LIMIT 1; ALTER TABLE t_part REMOVE PARTITIONING; EXPLAIN SELECT * FROM t_part ORDER BY k LIMIT 1;