How can I optimize this query ...?

Question

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

The routes contain a little over 9000 rows, and I indexed each column. There are only 2,000 lines at airports, and I also indexed each column.

When I ran this query, it may 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"

By running it with "DESCRIBE", I get followinf information, but I'm not 100% sure what it tells 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 come up with is to run two separate queries and combine them with PHP, although I cannot believe that something like this was something that could kill the mysql server. So, as usual, I suspect I'm doing something stupid. SQL is my number 1 weakness.

Answer 1

, .

Answer 2

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"

EXPLAIN PLAN , airports.IATA.

.

Name , UNIQUE, IATA .

Update:

. , :

SHOW CREATE TABLE airports

, FULLTEXT IATA , ft_max_word_len MySQL 3 .

4.

IATA 3, MySQL , FULLTEXT .

Answer 3

, . , "rows" , 5000 ? .

, , , . ? ? , mysql , , .

EDIT: IATA , , ? ( , ). mysql , .

Answer 4

, (.. LEFT), routes_build carrier, origin destination.

Answer 5

, . , , , , , . , , , .

, ?