Why so great difference in so simple query ?

[Dmitriy Gorbenko]

Please, look at this one:

mysql>
mysql> SELECT sql_no_cache
-> COUNT(DISTINCT `zip`.`zipcode`)
-> FROM
-> `zipcodes` as `zip`
-> WHERE
-> `zip`.`state` = (
-> SELECT
-> DISTINCT `loc`.`state_code`
-> FROM
-> `hp_location` as `loc`
-> WHERE
-> `loc`.`location_type` = "city"
-> AND `loc`.`state` = "New York"
-> );
+---------------------------------+
| COUNT(DISTINCT `zip`.`zipcode`) |
+---------------------------------+
| 2189 |
+---------------------------------+
1 row in set (0.03 sec)

mysql> SELECT sql_no_cache
-> COUNT(DISTINCT `zip`.`zipcode`)
-> FROM
-> `hp_location` as `loc`
-> INNER JOIN
-> `zipcodes` as `zip`
-> ON
-> `loc`.`state_code` = `zip`.`state`
-> WHERE
-> `loc`.`location_type` = "city"
-> AND `loc`.`state` = "New York";
+---------------------------------+
| COUNT(DISTINCT `zip`.`zipcode`) |
+---------------------------------+
| 2189 |
+---------------------------------+
1 row in set (9.99 sec)

mysql>

I can't assume why..
Here are some info about indexes:


mysql>
mysql> show index from zipcodes;
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| zipcodes | 0 | PRIMARY | 1 | zipcode_id | A | 42071 | NULL | NULL | | BTREE | |
| zipcodes | 0 | zipcode_city | 1 | zipcode | A | 42071 | NULL | NULL | | BTREE | |
| zipcodes | 0 | zipcode_city | 2 | city | A | 42071 | NULL | NULL | | BTREE | |
| zipcodes | 1 | state | 1 | state | A | 62 | NULL | NULL | YES | BTREE | |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.01 sec)

mysql> show index from hp_location;
+-------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| hp_location | 0 | PRIMARY | 1 | location_id | A | 159427 | NULL | NULL | | BTREE | |
| hp_location | 0 | hp_location_tag_name | 1 | tag_name | A | 159427 | NULL | NULL | | BTREE | |
| hp_location | 1 | hp_location_type | 1 | location_type | A | 26 | NULL | NULL | | BTREE | |
| hp_location | 1 | type_and_state | 1 | location_type | A | 6 | NULL | NULL | | BTREE | |
| hp_location | 1 | type_and_state | 2 | state | A | 346 | NULL | NULL | | BTREE | |
| hp_location | 1 | hp_location_zip | 1 | zip | A | 53142 | NULL | NULL | | BTREE | |
+-------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.02 sec)

mysql>

Any links to articles or any help will be appreciated.

Thanks,
Dmitriy.