Using indexes with IN statement

[Dmitriy Gorbenko]

I have an query:

SELECT
COUNT(DISTINCT zipcode)
FROM
zipcodes
WHERE
state IN (
SELECT
DISTINCT state_code
FROM
hp_location
WHERE
location_type = "city"
AND state = "New York"
);

And this query doesn't use index zipcodes.zipcode.
But then I change IN statement into equal "=" - index comes into using.

Can anyone tell my, why ?

Here more explain:

mysql>
mysql> EXPLAIN SELECT COUNT(DISTINCT zipcode) FROM zipcodes WHERE state IN ( SELECT DISTINCT state_code FROM hp_location WHERE location_type = "city" AND state = "New York")\G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: zipcodes
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 42071
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: hp_location
type: ref
possible_keys: hp_location_type,type_and_state
key: type_and_state
key_len: 259
ref: const,const
rows: 1164
Extra: Using where; Using temporary
2 rows in set (0.01 sec)

ERROR:
No query specified

mysql> EXPLAIN SELECT COUNT(DISTINCT zipcode) FROM zipcodes WHERE state = ( SELECT DISTINCT state_code FROM hp_location WHERE location_type = "city" AND state = "New York")\G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: zipcodes
type: ref
possible_keys: state
key: state
key_len: 4
ref: const
rows: 2047
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: hp_location
type: ref
possible_keys: hp_location_type,type_and_state
key: type_and_state
key_len: 259
ref:
rows: 1164
Extra: Using where; Using temporary
2 rows in set (0.01 sec)

ERROR:
No query specified


Thanks,
Dmitriy.