I've heard that sub-queries aren't well-optimized, but this case seems
ridiculous.
First, a little setup:
SELECT pub_id FROM pub WHERE pub_code = 'GD' INTO @P;
=== Inner Query by Itself ===
us-gii >SELECT prod_pub_prod_id FROM prod
-> WHERE pub_id = @P
-> AND prod_discont = 0
-> GROUP BY prod_pub_prod_id
-> HAVING COUNT(*) > 1;
+------------------+
| prod_pub_prod_id |
+------------------+
| NULL |
| GDAE0106ICR |
| GDME0002TR |
| GDME0023IAR |
| GDME0059IAR |
+------------------+
5 rows in set (0.05 sec)
us-gii >EXPLAIN
-> SELECT prod_pub_prod_id FROM prod
-> WHERE pub_id = @P
-> AND prod_discont = 0
-> GROUP BY prod_pub_prod_id
-> HAVING COUNT(*) > 1
-> \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id,pub_id_2
key: pub_id
key_len: 48
ref: const
rows: 1543
Extra: Using where; Using temporary; Using filesort
=== Outer Query without Inner Query ===
us-gii >SELECT prod_num FROM prod
-> WHERE pub_id = @P
-> AND prod_pub_prod_id IN
-> (
-> NULL,
-> 'GDAE0106ICR',
-> 'GDME0002TR',
-> 'GDME0023IAR',
-> 'GDME0059IAR'
-> )
-> ;
+----------+
| prod_num |
+----------+
| 83298 |
| 85092 |
| 88728 |
| 97231 |
| 97235 |
| 98368 |
| 107693 |
| 112461 |
+----------+
8 rows in set (0.01 sec)
us-gii >EXPLAIN
-> SELECT prod_num FROM prod
-> WHERE pub_id = @P
-> AND prod_pub_prod_id IN
-> (
-> NULL,
-> 'GDAE0106ICR',
-> 'GDME0002TR',
-> 'GDME0023IAR',
-> 'GDME0059IAR'
-> )
-> \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: prod_pub_prod_id,pub_id,pub_id_2
key: pub_id
key_len: 48
ref: const
rows: 1543
Extra: Using where
=== Outer Query with Sub-query ===
us-gii >EXPLAIN
-> SELECT prod_num FROM prod
-> WHERE pub_id = @P
-> AND prod_pub_prod_id IN
-> (SELECT prod_pub_prod_id FROM prod
-> WHERE pub_id = @P
-> AND prod_discont = 0
-> GROUP BY prod_pub_prod_id
-> HAVING COUNT(*) > 1)
-> \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: prod
type: ref
possible_keys: pub_id,pub_id_2
key: pub_id
key_len: 48
ref: const
rows: 1543
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: prod
type: index
possible_keys: pub_id,pub_id_2
key: prod_pub_prod_id
key_len: 768
ref: NULL
rows: 72
Extra: Using where; Using filesort
I don't know how long the Outer Query with Sub-query would take: I killed it
after several minutes. I'm guessing that it has to do with the fact that the
inner query is "dependent", but why is that happening?
=== Rewritten as Join ===
us-gii >SELECT prod_num FROM prod JOIN
-> (SELECT prod_pub_prod_id FROM prod
-> WHERE pub_id = @P
-> AND prod_discont = 0
-> GROUP BY prod_pub_prod_id
-> HAVING COUNT(*) > 1) AS x
-> ON prod.prod_pub_prod_id = x.prod_pub_prod_id
-> WHERE prod.pub_id = @P
-> AND prod.prod_discont = 0;
+----------+
| prod_num |
+----------+
| 98368 |
| 107693 |
| 83298 |
| 85092 |
| 88728 |
| 97231 |
| 97235 |
| 112461 |
+----------+
8 rows in set (0.05 sec)
us-gii >EXPLAIN
-> SELECT prod_num FROM prod JOIN
-> (SELECT prod_pub_prod_id FROM prod
-> WHERE pub_id = @P
-> AND prod_discont = 0
-> GROUP BY prod_pub_prod_id
-> HAVING COUNT(*) > 1) AS x
-> ON prod.prod_pub_prod_id = x.prod_pub_prod_id
-> WHERE prod.pub_id = @P
-> AND prod.prod_discont = 0
-> \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: prod
type: ref
possible_keys: prod_pub_prod_id,pub_id,pub_id_2
key: prod_pub_prod_id
key_len: 768
ref: x.prod_pub_prod_id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: prod
type: ref
possible_keys: pub_id,pub_id_2
key: pub_id
key_len: 48
ref:
rows: 1544
Extra: Using where; Using temporary; Using filesort
3 rows in set (0.03 sec)
=====
What a difference! I don't understand it, though.
Does anyone want to take on the challenge of educating me?
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
