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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0,5.1
• OS: Any
• CPU Architecture: Any
• Tags: explain, limit
• Triage: Triaged: D5 (Feature request)

[8 Jan 2010 2:16] Mark Callaghan

Description:
Docs for explain do not mention that 'rows' is inaccurate for LIMIT. By inaccurate I mean that a statement like:
   "explain select * from foo where ... LIMIT $x" 
returns a value in the 'rows' column of explain output that is constant regardless of the value of $x

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

How to repeat:
Using 5.1.38
>>>
echo "drop table if exists t1;"
echo "create table t1 (i int, j int, primary key (i,j)) engine=innodb;"

for i in $( seq 1 10000 ); do
echo "insert into t1 values ($i, $i);"
done

echo "explain select * from t1 where i < 100 LIMIT 1;"
echo "explain select * from t1 where i < 100 LIMIT 10;"
echo "explain select * from t1 where i < 100 LIMIT 50;"
>>>

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	99	Using where; Using index
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	99	Using where; Using index
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	99	Using where; Using index

Suggested fix:
Fix the docs for now and then fix EXPLAIN and/or the optimizer.

[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.