Wednesday, March 09, 2016

MariaDB - a surprise dependent subquery execution plan

Welcome myself into the MySQL world. While working with MariaDB 10.0.21, there are two queries as shown below, one uses alias and the other does not, seems to me they should be no difference in terms of functionality or performance.

-- query 1 with alias a and b
delete a FROM mydb.v_audit_info a 
       WHERE a.api_seq in  
                (SELECT cart_line_seq
                   FROM mydb.v_shopping_cart_item b
                  WHERE cart_seq = 127883 );
-- query 2 without alias a and b
delete  FROM mydb.v_audit_info  
       WHERE api_seq in  
                (SELECT cart_line_seq
                   FROM mydb.v_shopping_cart_item 
                  WHERE cart_seq = 127883 );


However, when I check the execution plan, I get quite different results: -- query 1
+------+-------------+-------+------+-----------------------------------------------------------+---------------------------+---------+-----------------------------+------+-------------+
| id   | select_type | table | type | possible_keys                                             | key                       | key_len | ref                         | rows | Extra       |
+------+-------------+-------+------+-----------------------------------------------------------+---------------------------+---------+-----------------------------+------+-------------+
|    1 | PRIMARY     | b     | ref  | PRIMARY,v_shopping_cart_item_udx1,idx2_shopping_cart_item | v_shopping_cart_item_udx1 | 9       | const                       |    1 | Using index |
|    1 | PRIMARY     | a     | ref  | idx1_audit_info                                           | idx1_audit_info           | 9       | mydb.b.cart_line_seq |    3 |             |
+------+-------------+-------+------+-----------------------------------------------------------+---------------------------+---------+-----------------------------+------+-------------+

-- query 2
+------+--------------------+----------------------+-----------------+-----------------------------------------------------------+---------+---------+------+-------+-------------+
| id   | select_type        | table                | type            | possible_keys                                             | key     | key_len | ref  | rows  | Extra       |
+------+--------------------+----------------------+-----------------+-----------------------------------------------------------+---------+---------+------+-------+-------------+
|    1 | PRIMARY            | v_audit_info         | ALL             | NULL                                                      | NULL    | NULL    | NULL | 60646 | Using where |
|    2 | DEPENDENT SUBQUERY | v_shopping_cart_item | unique_subquery | PRIMARY,v_shopping_cart_item_udx1,idx2_shopping_cart_item | PRIMARY | 8       | func |     1 | Using where |
+------+--------------------+----------------------+-----------------+-----------------------------------------------------------+---------+---------+------

In the execution plan for query 1, we first execute the subquery and obtain a list of cart_line_seq and then access the v_audit_info with primary key.

In the execution plan for query 2, the subquery becomes dependent, we need to scan 60646 rows from v_audit_info and for each row, checking the condition in the subquery.

Using the profiling, it can clearly see the huge difference in Duration:

+----------+------------+--------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                                                                              |
+----------+------------+--------------------------------------------------------------------+
|        1 | 0.00331659 | delete a FROM mydb.v_audit_info a
       WHERE a.api_seq in
                (SELECT cart_line_seq
                   FROM mydb.v_shopping_cart_item b
                  WHERE cart_seq = 127883 ) |
....


|       13 | 0.23299025 | delete  FROM mydb.v_audit_info
       WHERE api_seq in
                (SELECT cart_line_seq
                   FROM mydb.v_shopping_cart_item
                  WHERE cart_seq = 127883 )      |
+----------+------------+-------------------------------------------------------------------

Don't know why,but certainly I will suggest using the alias version.

1 comment:

Marko Sutic said...

I think I've seen somewhere that this is bug. This behaviour is not related only to MariaDB... I've just checked and you get the same behaviour on Percona XtraDB.

Didn't know that alias solves the problem.
Thanks for sharing.

Regards,
Marko