Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.2.14, 10.1(EOL)
-
None
Description
I noticed the following suboptimal choice of the optimizer in mariadb-5.2 (the similar behavior of the optimizer can be found in any version of mariadb or mysql). Frequently the optimizer discards as inferior the plan that
uses a compound key with major coinstant components for a join.
To demonstrate the problem I use a standard DBT-3 innodb database of scale factor 10 with one additional index on supplier(s_acctbal).
The problem can be demonstrated with the query:
select max(l_discount) from supplier, lineitem
where s_acctbal between 2900 and 2910 and s_suppkey=l_suppkey and l_partkey=304540;
For this query the optimizer chooses the plan:
MariaDB [dbt3sf10]> explain select max(l_discount) from supplier, lineitem where s_acctbal between 2900 and 2910 and s_suppkey=l_suppkey and l_partkey=304540\G |
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: lineitem |
type: ref
|
possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
|
key: i_l_suppkey_partkey |
key_len: 5
|
ref: const
|
rows: 58 |
Extra: Using where |
*************************** 2. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: supplier |
type: eq_ref
|
possible_keys: PRIMARY,i_s_acctbal |
key: PRIMARY |
key_len: 4
|
ref: dbt3sf10.lineitem.l_suppkey
|
rows: 1 |
Extra: Using where |
2 rows in set (0.00 sec) |
|
An execution by this plan took mariadb-5.2 about 0.06 sec.
However the following alternative plan is better as it took only 0.02 sec
MariaDB [dbt3sf10]> explain select straight_join max(l_discount) from supplier, lineitem where s_acctbal between 2900 and 2910 and s_suppkey=l_suppkey and l_partkey=304540\G |
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: supplier |
type: range
|
possible_keys: PRIMARY,i_s_acctbal |
key: i_s_acctbal |
key_len: 9
|
ref: NULL |
rows: 101 |
Extra: Using where; Using index |
*************************** 2. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: lineitem |
type: ref
|
possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
|
key: i_l_suppkey_partkey |
key_len: 10
|
ref: const,dbt3sf10.supplier.s_suppkey
|
rows: 3 |
Extra: Using where |
2 rows in set (0.00 sec) |
Most probably the optimizer does not take into account that when executing by the alternative plan the server accesses at most 3 pages of the table lineitem.