How can I optimize this query...?

Question

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.

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

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.

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.

Answer 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.

Answer 2

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.

Answer 3

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?

Answer 4

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.

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.

By default, it's 4.

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

Answer 5

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.

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.

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.