Добрый день.
Просьба, объясните безграмотному ) почему имеется довольно большая разница в выполнении двух с виду простых запросов:
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>
Как видите, в первом случае есть подзапрос (там кстати, если юзать IN вместо =, то ключ по `zip`.`state` работать не будет, что довольно интересно и непонятно), а во втором таблица подключается через INNER - по идее, скорость отработки должна быть одинакова, однако нет...
Вот информация о ключах:
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>
Подскажите пожалуйста, каким образом можно ускороть второй запрос (с JOIN).
Спасибо,
Дмитрий.
