Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.4
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
create table t1 (a int, b int); insert t1 values (rand()*1e5, rand()*1e5); insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; insert t1 select rand()*1e5, rand()*1e5 from t1; set use_stat_tables=preferably; analyze table t1; create table t2 (c int, d int, key(c), key(d)); insert t2 select floor(rand()*1e5/2)*2, floor(rand()*1e5/3)*3 from t1; set optimizer_use_condition_selectivity=1; explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; set optimizer_use_condition_selectivity=3; explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
One can see that column stats work, "filtered" column is correct:
MariaDB [test]> set optimizer_use_condition_selectivity=3; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; +------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 524288 | 2.00 | Using where | | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 524288 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | t2 | ref | c,d | c | 5 | test.t1.b | 10 | 100.00 | Using where | +------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) MariaDB [test]> set optimizer_use_condition_selectivity=1; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; +------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 524288 | 100.00 | Using where | | 1 | SIMPLE | t2 | ref | c,d | c | 5 | test.t1.b | 10 | 100.00 | | | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 524288 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+-----------+--------+----------+-------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)
But the first query takes almost twice as long as the second one.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Reviewed, patch approved.
The fix for this bug was pushed into the 10.0 tree.
"Stabilized" testcase provided over email