我如何优化这个查询…?

I have two tables, one for routes and one for airports.

我有两张桌子，一条是线路，另一张是机场。

Routes contains just over 9000 rows and I have indexed every column. Airports only 2000 rows and I have also indexed every column.

路由包含超过9000行，我已经索引了每一列。机场只有2000行，我也索引了每一列。

When I run this query it can take up to 35 seconds to return 300 rows:

当我运行这个查询时，最多需要35秒才能返回300行:

SELECT routes.* , a1.name as origin_name, a2.name as destination_name FROM routes
LEFT JOIN airports a1 ON a1.IATA = routes.origin
LEFT JOIN airports a2 ON a2.IATA = routes.destination
WHERE routes_build.carrier = "Carrier Name"

Running it with "DESCRIBE" I get the followinf info, but I'm not 100% sure on what it's telling me.

用“DESCRIBE”来运行它，我得到了如下的信息，但是我不能100%确定它告诉我什么。

id | Select Type   | Table             | Type   | possible_keys        | Key            | Key_len   | ref    | rows     | Extra
--------------------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE        | routes_build      | ref    | carrier,carrier_2    | carrier        | 678       | const  | 26       | Using where
--------------------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE        | a1                | ALL    | NULL                 | NULL           | NULL      | NULL   | 5389     |
--------------------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE        | a2                | ALL    | NULL                 | NULL           | NULL      | NULL   | 5389     |
--------------------------------------------------------------------------------------------------------------------------------------

The only alternative I can think of is to run two separate queries and join them up with PHP although, I can't believe something like this being something that could kill a mysql server. So as usual, I suspect I'm doing something stupid. SQL is my number 1 weakness.

我能想到的唯一的选择就是运行两个独立的查询并将它们与PHP结合起来，尽管如此，我无法相信这样的事情会破坏mysql服务器。像往常一样，我怀疑我在做傻事。SQL是我的头号弱点。

5 个解决方案

#1

Personally, I would start by removing the left joins and replacing them with inner joins as each route must have a start and end point.

就我个人而言，我将首先删除左连接，然后用内部连接替换它们，因为每个路由必须有一个起点和终点。

#2

It's telling you that it's not using an index for joining on the airports table. See how the "rows" column is so huge, 5000 odd? that's how many rows it's having to read to answer your query.

它告诉你它没有使用索引来连接机场表。看看“行”列是多么巨大，5000个奇数?这就是需要读取多少行才能回答查询。

I don't know why, as you have claimed you have indexed every column. What is IATA? Is it Unique? I believe if mysql decides the index is inefficient it may ignore it.

我不知道为什么，因为你说你已经索引了每一列。国际航空运输协会是什么?它是独特的吗?我认为如果mysql认为索引效率低下，它可能会忽略它。

EDIT: if IATA is a unique string, maybe try indexing half of it only? (You can select how many characters to index) That may give mysql an index it can use.

编辑:如果IATA是一个唯一的字符串，可能只尝试索引它的一半?(您可以选择要索引的字符数)，这可以为mysql提供一个可以使用的索引。

#3

SELECT  routes.*, a1.name as origin_name, a2.name as destination_name
FROM    routes_build
LEFT JOIN
        airports a1
ON      a1.IATA = routes_build.origin
LEFT JOIN
        airports a2
ON      a2.IATA = routes_build.destination
WHERE   routes_build.carrier = "Carrier Name"

From your EXPLAIN PLAN I can see that you don't have an index on airports.IATA.

从你的解释计划中我可以看出你对机场没有索引。

You should create it for the query to work fast.

您应该创建它，以便查询能够快速工作。

Name also suggests that it should be a UNIQUE index, since IATA codes are unique.

名称也表明它应该是一个唯一的索引，因为IATA代码是唯一的。

Update:

更新:

Please post your table definition. Issue this query to show it:

请张贴您的表定义。发出此查询以显示:

SHOW CREATE TABLE airports

Also I should note that your FULLTEXT index on IATA is useless unless you have set ft_max_word_len is MySQL configuration to 3 or less.

我还应该注意，IATA上的全文索引是无用的，除非您将ft_max_word_len配置为3或更少。

By default, it's 4.

默认情况下,它是4。

IATA codes are 3 characters long, and MySQL doesn't search for such short words using FULLTEXT with default settings.

IATA代码有3个字符长，MySQL没有使用默认设置的全文搜索如此短的单词。

#4

After you implement Martin Robins's excellent advice (i.e. remove every instance of the word LEFT from your query), try giving routes_build a compound index on carrier, origin, and destination.

在您实现了Martin Robins的优秀建议(即从查询中删除单词的每个实例)之后，尝试在carrier、origin和destination上为routes_build复合索引。

#5

It really depends on what information you're trying to get to. You probably don't need to join airports twice and you probably don't need to use left joins. Also, if you can search on a numeric field rather than a text field, that would speed things up as well.

这取决于你想要得到什么信息。您可能不需要两次加入机场，也可能不需要使用左连接。此外，如果您可以在数字字段而不是文本字段上搜索，这也会加快搜索速度。

So what are you trying to fetch?

那你想拿什么?

#1