|
create table t1 (
|
a int,
|
filler1 char(128),
|
filler2 char(128),
|
key(a)
|
);
|
|
insert into t1
|
select A.a+10*B.a+100*C.a, repeat('abc-',32), repeat('abc-',32)
|
from ten A, ten B, ten C;
|
EXPLAIN shows the correct estimate, we will read 10 rows:
explain select a from t1 order by a limit 10;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | index | NULL | a | 5 | NULL | 10 | Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
ANALYZE's rows is different from the EXPLAIN:
analyze select a from t1 order by a limit 10;
|
MariaDB [j1]> analyze select a from t1 order by a limit 10;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+-------------+
|
| 1 | SIMPLE | t1 | index | NULL | a | 5 | NULL | 978 | 10.00 | 100.00 | 100.00 | Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+-------------+
|
The value is wrong, it ignores the LIMIT clause.
|