Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 5.2.14
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 770012