| Bug #4114 | order by - limit optimization is broken for "range" access type. | ||
|---|---|---|---|
| Submitted: | 12 Jun 2004 2:27 | Modified: | 24 Aug 2004 6:49 |
| Reporter: | Peter Zaitsev (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.2 | OS: | Any (all) |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[12 Jun 2004 2:27]
Peter Zaitsev
[21 Aug 2004 9:35]
Sergey Petrunya
With today's 4.1 pull, both explain select * from s1,s2 where s2.n =7 order by s1.i limit 5; explain select * from s1,s2 where s2.n in (7) order by s1.i limit 5; produce the same output: +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | s1 | index | NULL | PRIMARY | 4 | NULL | 85 | | | 1 | SIMPLE | s2 | ref | n | n | 5 | const | 1 | Using where | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ The query explain select * from s1,s2 where s2.n in (s1.s) order by s1.i limit 5 produces this: +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------+ | 1 | SIMPLE | s1 | ALL | NULL | NULL | NULL | NULL | 85 | Using temporary; Using filesort | | 1 | SIMPLE | s2 | index | NULL | i | 9 | NULL | 37 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------+ How important is handling for the last query? Should I investigate further?
[24 Aug 2004 4:27]
Peter Zaitsev
Original bug was fixed which is great, however it looks like it was fixed only partially: Single value IN seems to be transformed to "=" for constants however it is not transformed if there is single value in the list, which is not constant however, while it probably should: mysql> explain select count(*) from s1,s2 where s2.n in (s1.s); +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | s1 | index | NULL | s | 4 | NULL | 85 | Using index | | 1 | SIMPLE | s2 | index | NULL | n | 5 | NULL | 37 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 2 rows in set (0.00 sec) mysql> explain select count(*) from s1,s2 where s2.n = s1.s; +----+-------------+-------+-------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+-----------+------+-------------+ | 1 | SIMPLE | s2 | index | n | n | 5 | NULL | 37 | Using index | | 1 | SIMPLE | s1 | ref | s | s | 4 | test.s2.n | 10 | Using index | +----+-------------+-------+-------+---------------+------+---------+-----------+------+-------------+ 2 rows in set (0.00 sec) There is even bigger problem here which I would call "dynamic ranges": mysql> explain select * from s1,s2 where s2.n in (s1.s,s1.s+1); +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | s1 | ALL | NULL | NULL | NULL | NULL | 85 | | | 1 | SIMPLE | s2 | index | NULL | i | 9 | NULL | 37 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 2 rows in set (0.00 sec) As you see index scan is used for accessing second table each time as MySQL for some reason can't use dynamic range matching for each iteration. This is serious problem for some complex queries. It is up to you guys to consider this a bug or just defficiency but in any case I'd like to see this queries in broken queries list and scheduled to be fixed sometime according to your priorities.
[24 Aug 2004 6:49]
Igor Babaev
We do not transform <expr> IN (<value>) to expr=<value> currently in 4.1. And we are not going to add this transformation there. It will appear in 5.0.x
