Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5.25, 5.3.7
-
None
-
None
Description
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.