| Bug #34124 | EXPLAIN EXTENDED shows bogus value for 'filtered' column for LIMIT query | ||
|---|---|---|---|
| Submitted: | 29 Jan 2008 4:13 | Modified: | 27 Mar 2015 3:12 |
| Reporter: | Sergey Petrunya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1-bk | OS: | Any |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
| Tags: | regression | ||
[29 Jan 2008 5:00]
Valeriy Kravchuk
Looks like some kind of recent regression, as 5.1.22 does NOT show this exact problem:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3310
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.22-rc-community-debug MySQL Community Server - Debug (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table ten (a int);
Query OK, 0 rows affected (0.28 sec)
mysql> insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
Query OK, 10 rows affected (0.06 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> create table t1 (a int, filler char(200), key(a));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t1 select A.a + 10*(B.a + 10* C.a) , 'filler'
-> from ten A, ten B, ten C;
Query OK, 1000 rows affected (0.17 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> explain extended select * from t1 order by a limit 20\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: a
key_len: 5
ref: NULL
rows: 1049
filtered: 100.00
Extra:
1 row in set, 1 warning (0.06 sec)
(t1 was created as InnoDB, for MyISAM rows estimation is 1000).
[29 Jan 2008 7:20]
Sveta Smirnova
Thank you for the report. Verified as described.
[5 Oct 2014 18:54]
Rick James
Still a problem in 5.6.12.
[27 Mar 2015 3:12]
Paul DuBois
Noted in 5.7.7, 5.8.0 changelogs. EXPLAIN could show incorrect filtered values for queries that included a LIMIT clause.
Description: EXPLAIN EXTENDED can show bogus value (> 100%) for Extra column for LIMIT query. How to repeat: Run this: create table ten (a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, filler char(200), key(a)); insert into t1 select A.a + 10*(B.a + 10* C.a) , 'filler' from ten A, ten B, ten C; mysql> explain extended select * from t1 order by a limit 20; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: NULL key: a key_len: 5 ref: NULL rows: 20 filtered: 5000.00 Extra: 1 row in set, 1 warning (0.00 sec) And see 'filtered' have a value of 5000 which doesn't make any sense as it is percentage of records that will be filtered out. I was using 5.1, debug build, tip cset ChangeSet@1.2654, 2008-01-17 18:36:04+01:00, mhansson@linux-st28.site +3 -0 but it should be repeatable on any 5.1. Suggested fix: I think the problem is only with EXPLAIN, because LIMIT calculations are done after the join order has been chosen. But this needs to be verified.