Bug #50168 | The value of 'rows' in EXPLAIN output is not accurate for LIMIT | ||
---|---|---|---|
Submitted: | 8 Jan 2010 2:16 | Modified: | 13 Jan 2010 17:05 |
Reporter: | Mark Callaghan | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.0,5.1 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | explain, limit |
[8 Jan 2010 2:16]
Mark Callaghan
[8 Jan 2010 15:45]
Stefan Hinz
Documenting this odd behavior doesn't seem the right thing to do. First it should be assessed by an Optimizer developer, to see if the underlying reason is a bug.
[13 Jan 2010 16:52]
Mark Callaghan
Behavior has improved from 5.0 to 5.1 when there is no WHERE clause: 5.0.84 mysql> explain select * from t1 order by i limit 10; +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------+ | 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 15879 | | 5.1.38 mysql> explain select * from t1 order by i limit 10; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 10 | |
[13 Jan 2010 16:54]
Mark Callaghan
It would help if something in the EXPLAIN output indicated that LIMIT was used and where it was used. That is not done now.
[13 Jan 2010 17:05]
Mark Callaghan
Wow, 5.1.38 even gets this some of this right with joins ... 5.0.84 mysql> explain select * from t1 a, t1 b where a.i=b.i order by a.i limit 10; +----+-------------+-------+--------+---------------+---------+---------+----------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+----------+-------+-------+ | 1 | SIMPLE | a | index | PRIMARY | PRIMARY | 4 | NULL | 17144 | | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.i | 1 | | +----+-------------+-------+--------+---------------+---------+---------+----------+-------+-------+ 2 rows in set (0.00 sec) mysql> explain select * from t1 a, t1 b where a.i=b.i order by a.i limit 100; +----+-------------+-------+--------+---------------+---------+---------+----------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+----------+-------+-------+ | 1 | SIMPLE | a | index | PRIMARY | PRIMARY | 4 | NULL | 17144 | | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.i | 1 | | 5.1.38 mysql> explain select * from t1 a, t1 b where a.i=b.i order by a.i limit 10; +----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+ | 1 | SIMPLE | a | index | PRIMARY | PRIMARY | 4 | NULL | 10 | | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.i | 1 | | +----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+ 2 rows in set (0.00 sec) mysql> explain select * from t1 a, t1 b where a.i=b.i order by a.i limit 100; +----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+ | 1 | SIMPLE | a | index | PRIMARY | PRIMARY | 4 | NULL | 100 | | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.i | 1 | |
[21 Jan 2010 21:20]
Morgan Tocker
Mark, I recently discovered the change in 5.1 to account for LIMIT in rows estimation is not trouble-free: http://bugs.mysql.com/bug.php?id=50394
[25 Mar 2011 17:38]
Valeriy Kravchuk
Bug #59670 was marked as a duplicate of this one.