Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.19
-
None
-
EITs unused in non-indexed join conditions.
Description
During optimization, when a table is joined onto the tables already in the
plan, table_multi_eq_cond_selectivity() is meant to account for the
multiple equalities tying its columns to theirs that the chosen access
method does not already use: for each such equality, the largest
avg_frequency/rows over its fields on the earlier tables, multiplied
together across equalities.
It never did. The accumulator starts at 1.0 and is only ever raised:
double eq_fld_sel= 1.0;
...
if (curr_eq_fld_sel < 1.0)
set_if_bigger(eq_fld_sel, curr_eq_fld_sel);
Every candidate is below 1.0, so eq_fld_sel stays 1.0 and the function returns 1.0 for every table in every plan. This has been so since the function was added (fc1c8ffdadf, the MWL#253 pilot patch).
SET optimizer_use_condition_selectivity= 4; |
SET use_stat_tables= PREFERABLY; |
SET join_cache_level= 2; |
|
|
CREATE TABLE dim (d_id INT PRIMARY KEY, d_name VARCHAR(20)) ENGINE=InnoDB; |
INSERT INTO dim SELECT seq, CONCAT('name', seq) FROM seq_1_to_20; |
|
|
# fact.d_id has no index, so the join to dim can only be a BNL join |
CREATE TABLE fact (f_id INT PRIMARY KEY, d_id INT NOT NULL, val INT) ENGINE=InnoDB; |
INSERT INTO fact SELECT seq, seq % 20 + 1, seq FROM seq_1_to_2000; |
|
|
CREATE TABLE detail (f_id INT PRIMARY KEY, extra INT) ENGINE=InnoDB; |
INSERT INTO detail SELECT seq, seq * 2 FROM seq_1_to_2000; |
|
|
ANALYZE TABLE dim, fact, detail PERSISTENT FOR ALL; |
|
|
# one of 20 dim rows survives, so one twentieth of fact joins it and detail is looked up 100 times |
EXPLAIN EXTENDED
|
SELECT STRAIGHT_JOIN COUNT(*) FROM dim, fact, detail |
WHERE fact.d_id = dim.d_id AND detail.f_id = fact.f_id AND dim.d_name = 'name7'; |
|
|
--source include/analyze-format.inc
|
ANALYZE FORMAT=JSON
|
SELECT STRAIGHT_JOIN COUNT(*) FROM dim, fact, detail |
WHERE fact.d_id = dim.d_id AND detail.f_id = fact.f_id AND dim.d_name = 'name7'; |
|
|
DROP TABLE dim, fact, detail; |
