-- 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.
