|
In some cases optimizer chooses an execution plan that is usually faster, but is much slower when only a few result rows are
Take DBT-3 data with MyISAM tables:
create view v_orders as
|
select * from
|
orders join
|
customer on (o_custkey = c_custkey) join
|
nation on (c_nationkey = n_nationkey) join
|
region on (n_regionkey = r_regionkey)
|
A simple
select * from v_orders limit 3
|
uses the following plan
+------+-------------+----------+------+-----------------------+---------------+---------+--------------------------+--------+---------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+------+-----------------------+---------------+---------+--------------------------+--------+---------------------------------------------------------------------------+
|
| 1 | SIMPLE | region | ALL | PRIMARY | NULL | NULL | NULL | 5 | |
|
| 1 | SIMPLE | nation | ref | PRIMARY,i_n_regionkey | i_n_regionkey | 5 | dbt3m.region.r_regionkey | 5 | Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan |
|
| 1 | SIMPLE | customer | ref | PRIMARY,i_c_nationkey | i_c_nationkey | 5 | dbt3m.nation.n_nationkey | 180000 | Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan |
|
| 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | dbt3m.customer.c_custkey | 15 | Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan |
|
+------+-------------+----------+------+-----------------------+---------------+---------+--------------------------+--------+---------------------------------------------------------------------------+
|
Same plan is used without LIMIT clause.
It requires join buffers to be filled before any result rows can be produced,
and the query takes a minute to complete (with LIMIT 3).
With mrr=off the same query returns in a fraction of a second.
|