如何使用join改进此MySQL查询? [英] How to improve this MySQL Query using join?

问题描述

我有一个简单的查询，它花费了超过14秒的时间.

I have got a simple query and it takes more than 14 seconds.

select 
     e.title, e.date, v.name, v.city, v.region, v.country

from seminar e force index for join (venueid) 
     left join venues v on e.venueid = v.id 

where v.country = 'US'
     and v.city = 'New York' 
     and v.region = 'NY'
     and e.date > curdate() 
     and e.someid != 0

注意:count(e.id)表示调试的缩写.实际上，我们从两个表中都获得了信息.

说明如下:

+----+-------------+-------+-------------+--------------------------------------------------------------------------------------+--------------------------+---------+-----------------+------+--------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                                                        | key                      | key_len | ref             | rows | Extra                                                  |
+----+-------------+-------+-------------+--------------------------------------------------------------------------------------+--------------------------+---------+-----------------+------+--------------------------------------------------------+
|  1 | SIMPLE      | v     | index_merge | PRIMARY,city,country,region | city,region | 378,378 | NULL            |    2 | Using intersect(city,region); Using where |
|  1 | SIMPLE      | e     | ref         | venueid                     |  venueid    | 5       | v.id            |   11 | Using where                                            |
+----+-------------+-------+-------------+--------------------------------------------------------------------------------------+--------------------------+---------+-----------------+------+--------------------------------------------------------+

我有e.id，e.date，e.someid以及v.id，v.country，v.city和v.region的索引.

I have indexes on e.id, e.date, e.someid, as well as v.id, v.country, v.city and v.region.

我知道数据库设置是一团糟，但这就是我现在必须处理的问题.

I know the db-setup is a mess but that's what I have to deal with right now.

为什么SQL会花这么长时间，直到最后会有一个大约.数150?在活动中，大约有100万个条目，在场地中，则有大约10万个条目.

Why does the SQL take so long as in the end there will be an approx. count 150? In events there are about 1M entries and in venues about 100K.

两个表都是MyISAM.有什么想法可以改善这一点吗?

Both tables are MyISAM. Any ideas how to improve this?

在创建这样的索引后

create index location on venues (city, region, country)

需要20秒，解释是这样的:

it takes 20 seconds, the explain is this:

+----+-------------+-------+------+--------------------------------------+--------------+---------+-------------------+------+------------------------------------+
| id | select_type | table | type | possible_keys                        | key          | key_len | ref               | rows | Extra                              |
+----+-------------+-------+------+--------------------------------------+--------------+---------+-------------------+------+------------------------------------+
|  1 | SIMPLE      | v     | ref  | PRIMARY,city,country,region,location | location     | 765     | const,const,const |  410 | Using index condition; Using where |
|  1 | SIMPLE      | e     | ref  | EventVenueID                         | venueid      | 5       | v.id              |   11 | Using where                        |
+----+-------------+-------+------+--------------------------------------+--------------+---------+-------------------+------+------------------------------------+

推荐答案

我走了另一条路，因为看来MySQL无法有效处理联接:

I went another way since it seems that MySQL can't handle joins effectively:

• 使用联接中需要的所有列创建了一个新的大表
• 所以研讨会和活动现在放在一张桌子上
• 添加索引

现在查询速度很快.不知道为什么...

Now the query is fast. Don't know why...

这就是我想要的.

如果仍然有人知道原因，我们非常欢迎您提供答案.

If anybody still knows why, you are more than welcome to provide an answer.

这篇关于如何使用join改进此MySQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！