MySQL Forums
Forum List  »  Performance

Slow subquery performance
Posted by: Sam Sgro
Date: November 04, 2004 05:00PM

I've got a subquery that's giving me miserable performance, brought on by using "IN" instead of "=". The two tables I'm using share nothing in common aside from the name of one column, "gi": one query returns instantly, and the other takes two hours. They don't have common foreign key constraints, or anything of that nature.

Why would using "IN" to consider one returned row instead of "=" cause MySQL's optimization of the query to change?

What follows is some background data.

mysql> SELECT DISTINCT access FROM accdb WHERE gi = (SELECT gi FROM redund WHERE rordinal=1 AND rgroup = (1925169));
+----------+
| access |
+----------+
| CAA47393 |
+----------+
1 row in set (0.00 sec)

mysql> explain SELECT DISTINCT access FROM accdb WHERE gi = (SELECT gi FROM redund WHERE rordinal=1 AND rgroup = (1925169))\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: accdb
type: ref
possible_keys: PRIMARY,iaccdb_gi
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: Using where; Using index; Using temporary
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: redund
type: ref
possible_keys: iredund_ordinal,iredund_rgroup
key: iredund_rgroup
key_len: 5
ref:
rows: 1
Extra:
2 rows in set (0.00 sec)

Adding the "IN" clause, and suddenly the subquery is a DEPENDENT SUBQUERY, and the PRIMARY query ends up having to scan 70 million rows:

mysql> explain SELECT DISTINCT access FROM accdb WHERE gi IN (SELECT gi FROM redund WHERE rordinal=1 AND rgroup = (1925169))\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: accdb
type: index
possible_keys: NULL
key: PRIMARY
key_len: 39
ref: NULL
rows: 73237473
Extra: Using where; Using index; Using temporary
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: redund
type: unique_subquery
possible_keys: PRIMARY,iredund_gi,iredund_ordinal,iredund_rgroup
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using index; Using where
2 rows in set (0.00 sec)

The definition of DEPENDENT SUBQUERY in the EXPLAIN section of the manual makes me think that the query is getting fouled by some relationship between the two, but I don't see how.

DEPENDENT SUBQUERY
First SELECT, dependent on outer subquery.

Any suggestions? (Aside from rewrite this as a JOIN? If I need to, I will, but I'd like to understand what's going wrong here first.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow subquery performance
9462
November 04, 2004 05:00PM
4119
November 05, 2004 12:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.