mysql - 查询慢是怎么回事？

Question

我有以下查询:

explain select full_name country_name, cc.country_code_id country_id, 
r.name region_name, r.region_id, wc.accentcity city_name, wc.city_id,
lower(concat_ws(' ', wc.city, r.name, cc.full_name)) as search1,
lower(concat_ws(' ', wc.city, cc.full_name)) as search2
from worldcities wc
inner join regions r on wc.region = r.region_id
inner join country_codes cc on wc.country = cc.country_code_id
-- where city like 'paris%' and full_name like 'fr%'
having 
search1 like 'paris f%' or 
search2 like 'paris f%';

输出如下:

+----+-------------+-------+--------+----------------+----------------+---------+-----------------------------+------+-------------+
| id | select_type | table | type   | possible_keys  | key            | key_len | ref                         | rows | Extra       |
+----+-------------+-------+--------+----------------+----------------+---------+-----------------------------+------+-------------+
|  1 | SIMPLE      | cc    | index  | PRIMARY        | full_name      | 144     | NULL                        |  315 | Using index |
|  1 | SIMPLE      | wc    | ref    | country_region | country_region | 4       | vpromote.cc.country_code_id | 2544 |             |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY        | PRIMARY        | 4       | vpromote.wc.region          |    1 |             |
+----+-------------+-------+--------+----------------+----------------+---------+-----------------------------+------+-------------+

这是世界城市的索引

+-------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| worldcities |          0 | PRIMARY        |            1 | city_id     | A         |     3170651 |     NULL | NULL   |      | BTREE      |         |               |
| worldcities |          1 | city           |            1 | city        | A         |     3170651 |     NULL | NULL   |      | BTREE      |         |               |
| worldcities |          1 | country_region |            1 | country     | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| worldcities |          1 | country_region |            2 | region      | A         |        1254 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

以下是表格:

mysql> explain worldcities;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| city_id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| country    | int(10) unsigned | NO   | MUL | NULL    |                |
| region     | int(10) unsigned | NO   |     | NULL    |                |
| city       | varchar(65)      | NO   | MUL | NULL    |                |
| accentcity | varchar(65)      | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

mysql> explain regions;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| region_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| country   | int(10) unsigned | YES  |     | NULL    |                |
| region    | char(2)          | NO   | MUL | NULL    |                |
| name      | varchar(115)     | NO   | MUL | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

mysql> explain country_codes;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| country_code_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| full_name       | char(48)         | NO   | MUL | NULL    |                |
| short_code      | char(3)          | NO   | MUL | NULL    |                |
| listing_order   | int(11)          | NO   |     | 0       |                |
+-----------------+------------------+------+-----+---------+----------------+

我不知道该怎么做才能加快速度，这个查询大约需要 9 秒才能运行。
我有另一个查询，耗时不到 1 秒，但它不返回结果，如下所示:

select full_name country_name, cc.country_code_id country_id, 
r.name region_name, r.region_id, wc.accentcity city_name, wc.city_id
from worldcities wc
inner join regions r on wc.region = r.region_id
inner join country_codes cc on wc.country = cc.country_code_id and r.country = cc.country_code_id
where full_name like 'fr%'

那么，我该怎么做才能加快第一个速度？

Answer 1

为什么不这样做:

select full_name country_name, cc.country_code_id country_id, 
r.name region_name, r.region_id, wc.accentcity city_name, wc.city_id,
lower(concat_ws(' ', wc.city, r.name, cc.full_name)) as search1,
lower(concat_ws(' ', wc.city, cc.full_name)) as search2
from (select accentcity city_name, city_id, city from worldcities where city='paris') as wc
inner join regions r on wc.region = r.region_id
inner join country_codes cc on wc.country = cc.country_code_id
-- where city like 'paris%' and full_name like 'fr%'
;

(或城市如“paris%”，具体取决于城市名称)