Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.22, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
Description
explain select count(*) from( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 3 AND d_id = 8 )) as L;
|
+------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
|
| 1 | PRIMARY | bmsql_stock | ref | PRIMARY | PRIMARY | 4 | const | 187638 | Using where |
|
| 3 | MATERIALIZED | bmsql_district | const | PRIMARY | PRIMARY | 8 | const,const | 1 | |
|
| 3 | MATERIALIZED | bmsql_order_line | range | PRIMARY | PRIMARY | 12 | NULL | 191 | Using where |
|
+------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
|
if only running the inner query it gives without outer count
SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 3 AND d_id = 8 ) ;
|
+--------+--------+------------+
|
| s_w_id | s_i_id | s_quantity |
|
+--------+--------+------------+
|
| 3 | 16009 | 14 |
|
| 3 | 16185 | 15 |
|
| 3 | 26154 | 12 |
|
| 3 | 36457 | 12 |
|
| 3 | 36490 | 15 |
|
| 3 | 42634 | 12 |
|
| 3 | 55333 | 13 |
|
| 3 | 76808 | 14 |
|
| 3 | 76918 | 13 |
|
| 3 | 76937 | 15 |
|
+--------+--------+------------+
|
10 rows in set (0.00 sec)
|
now i just count this and the plan
[+------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
|
| 1 | PRIMARY | bmsql_stock | ref | PRIMARY | PRIMARY | 4 | const | 187638 | Using where |
|
| 3 | MATERIALIZED | bmsql_district | const | PRIMARY | PRIMARY | 8 | const,const | 1 | |
|
| 3 | MATERIALIZED | bmsql_order_line | range | PRIMARY | PRIMARY | 12 | NULL | 191 | Using where |
|
+------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
|
now disabling derived_merge goes back to regular plan
Is this expected that there is no computation of the materialization result in such case ?
set optimizer_switch='derived_merge=off';
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
 |
MariaDB [sabre]> explain select count(*) from( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 3 AND d_id = 8 )) as L;
|
+------+-------------+------------------+--------+---------------------+---------+---------+--------------------------------------+------+----------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------------+--------+---------------------+---------+---------+--------------------------------------+------+----------------------------------------------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 191 | |
|
| 2 | DERIVED | bmsql_district | const | PRIMARY | PRIMARY | 8 | const,const | 1 | |
|
| 2 | DERIVED | bmsql_order_line | range | PRIMARY | PRIMARY | 12 | NULL | 191 | Using where; Start temporary |
|
| 2 | DERIVED | bmsql_stock | eq_ref | PRIMARY,s_item_fkey | PRIMARY | 8 | const,sabre.bmsql_order_line.ol_i_id | 1 | Using where; End temporary; Using join buffer (flat, BKA join); Key-ordered scan |
|
+------+-------------+------------------+--------+---------------------+---------+---------+--------------------------------------+------+----------------------------------------------------------------------------------+
|
4 rows in set (0.01 sec)
|