Slow subquery performance

[Sam Sgro]

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