Skip to content

Instantly share code, notes, and snippets.

View gist:6f73ecf6c49e972cb8630d7446c59a61
The code used to be:
if ( s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
{
...
if (filter)
{
...
}
type= JT_RANGE;
View gist:83e541a47f04cd0445690718ad951e01
=== Old execution (like before the patch) ===
update series set val=1; SELECT t, next_seq_value() r FROM t1 IGNORE INDEX(t) GROUP BY t HAVING r = 1 ORDER BY t1.u;
+------+------+
| t | r |
+------+------+
| 10 | 1 |
| 12 | 1 |
| 14 | 1 |
View q24b-10.11-print-loops.txt
{
"query_optimization": {
"r_total_time_ms": 14.02244903
},
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 193.6059154,
"nested_loop": [
{
View gist:b62430ed7e0c6f86646a4c96416c1568
MariaDB [test]> create table t1 (a int, b varchar(32), c int, d int, key(a,b(2),c));
Query OK, 0 rows affected (0.042 sec)
MariaDB [test]> insert into t1 select seq, '12345678', seq, seq from seq_1_to_10000;
Query OK, 10000 rows affected (3.380 sec)
Records: 10000 Duplicates: 0 Warnings: 0
MariaDB [test]> explain format=json select * from t1 where a between 2 and 4 and c=3;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN
View 10.11-selectivity-analyze.txt
+------+-------------+----------+--------+-----------------------------------+---------------+---------+---------------------------+-------+----------+----------+------------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+----------+--------+-----------------------------------+---------------+---------+---------------------------+-------+----------+----------+------------+----------------------------------------------+
| 1 | SIMPLE | region | ALL | PRIMARY | NULL | NULL | NULL | 5 | 5.00 | 20.00 | 20.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | n1 | ref | PRIMARY,i_n_regionkey | i_n_regionkey | 5 | dbt3.region.r_regionkey
View 10.11-clean-analyze.txt
+------+-------------+----------+--------+---------------------------+-------------+---------+---------------------------+---------+------------+----------+------------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+----------+--------+---------------------------+-------------+---------+---------------------------+---------+------------+----------+------------+-------------------------------------------------+
| 1 | SIMPLE | region | ALL | PRIMARY | NULL | NULL | NULL | 5 | 5.00 | 20.00 | 20.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | part | ALL | PRIMARY | NULL | NULL | NULL | 2000000 | 2000000.00
View gist:c52932bd7c8db0f53dd23942f20b6889
The only difference is Q8 which shows a slowdown:
08 | 4.3772793333 | 25.3670206667 |
10.11-vanilla
+------+-------------+----------+--------+----------------------------------------------------------------------------------------+-------------+---------+---------------------------+---------+----------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+----------+--------+----------------------------------------------------------------------------------------+-------------+---------+---------------------------+---------+----------+-------------------------------------------------+
| 1 | SIMPLE | region | ALL | PRIMARY
View fix-ratio.diff
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 91b7121bd8d..0b76a5bef46 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -3458,9 +3458,11 @@ bool Firstmatch_picker::check_qep(JOIN *join,
@@optimizer_switch allows join buffering.
- read_time is the same (i.e. FirstMatch doesn't add any cost
- remove fanout added by the last table
+ (if it is more than 1)
*/
View 1.diff
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index f121a592d5b..ac447286a56 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -3635,12 +3636,12 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join,
if (p->table->emb_sj_nest)
{
- sj_inner_fanout= COST_MULT(sj_inner_fanout, p->records_read);
+ sj_inner_fanout= COST_MULT(sj_inner_fanout, p->records_out);
View 1.diff
git diff ../client/mysqltest.cc
diff --git a/client/mysqltest.cc b/client/mysqltest.cc
index c462e9ee662..1a2928d2fe8 100644
--- a/client/mysqltest.cc
+++ b/client/mysqltest.cc
@@ -8582,6 +8582,7 @@ void run_query_stmt(struct st_connection *cn, struct st_command *command,
var_set_errno(mysql_stmt_errno(stmt));
+ display_optimizer_trace(cn, ds);