我无法从您的表中看到所使用的数据类型，因此这是一个简短的回答：
您仅从酒店表中选择数据，因此：
不需要一个小组
不需要内部连接，使用左连接。
以及：
你是按等级排序的，试着把它作为一个索引。
where name in，使用where airport.name='vienna'可能提高速度

把问题归结为基本问题。。。

DROP TABLE IF EXISTS hotel;

CREATE TABLE hotel 
(ID SERIAL PRIMARY KEY
,rating float NOT NULL
);

-- populated with 4096 hotels

DROP TABLE IF EXISTS term;

CREATE TABLE term 
(ID SERIAL PRIMARY KEY
,hotel_ID int NOT NULL
,KEY (hotel_ID)
);

-- populated with 16384 terms

DROP TABLE IF EXISTS airport;
CREATE TABLE airport 
(ID SERIAL PRIMARY KEY
,name varchar(255) NOT NULL UNIQUE
);

-- populated with 50 airports

DROP TABLE IF EXISTS airport_term;
CREATE TABLE airport_term 
(term_ID INT NOT NULL
,airport_ID INT NOT NULL
,PRIMARY KEY (term_ID,airport_ID)
);

-- populated with 1403 airport_term pairs

 SELECT DISTINCT h.* 
  FROM hotel h
  JOIN term t 
    ON t.hotel_ID = h.ID 
  JOIN airport_term ta 
    ON ta.term_ID = t.ID 
  JOIN airport a
    ON a.ID = ta.airport_ID 
 WHERE a.name IN ('Vienna', 'Berlin', 'Prague') 
 ORDER 
    BY h.ID 
     , h.rating DESC

-- returns 72 rows in zero seconds, as follows (condensed):

+-----+------------+
| ID  | rating     |
+-----+------------+
|  45 |  0.0494382 |
|  57 |   0.637326 |
...
| 480 |   0.837546 |
| 481 |   0.860047 |
| 486 |  0.0134837 |
...
| 770 |   0.995263 |
| 787 |   0.590259 |
| 801 |   0.102722 |
| 808 |   0.874417 |
| 813 |   0.217236 |
...
| 885 |   0.405265 |
| 887 |   0.437901 |
| 897 |   0.720929 |
| 901 |    0.84102 |
| 903 |   0.139152 |
| 908 |   0.600746 |
| 909 |   0.502444 |
| 992 |   0.631546 |
+-----+------------+

EXPLAIN 
SELECT DISTINCT h.* 
  FROM hotel h
  JOIN term t 
    ON t.hotel_ID = h.ID 
  JOIN airport_term ta 
    ON ta.term_ID = t.ID 
  JOIN airport a
    ON a.ID = ta.airport_ID 
 WHERE a.name IN ('Vienna', 'Berlin', 'Prague') 
 ORDER 
    BY h.ID 
     , h.rating DESC

+----+-------------+-------+--------+---------------------+---------+---------+--------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys       | key     | key_len | ref                | rows | Extra                                        |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------+------+----------------------------------------------+
|  1 | SIMPLE      | ta    | index  | PRIMARY             | PRIMARY | 8       | NULL               | 1403 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,ID,name     | PRIMARY | 8       | test.ta.airport_ID |    1 | Using where                                  |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY,ID,hotel_ID | PRIMARY | 8       | test.ta.term_ID    |    1 | Using where                                  |
|  1 | SIMPLE      | h     | eq_ref | PRIMARY,ID          | PRIMARY | 8       | test.t.hotel_ID    |    1 | Using where                                  |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------+------+----------------------------------------------+

查看查询以及优化器可能遇到的瓶颈，尝试添加以下索引。

ALTER TABLE airport_term
ADD INDEX (airport_ID, term_ID)

正如您当前的查询一样，它可能正在查找 airport 先上桌，拿到table airport_ID 然后还要翻阅电视上的每一张唱片 airport_term 因为它没有办法很快找到 term_ID 从 airport_ID .
通过允许快速查找 term_ID 从 airport_ID 在那20万张唱片中。

我使用子查询而不是join将时间缩短了1/2。运行查询需要1-2秒。不太理想，但肯定有进步。我仍然需要加入酒店的子查询中进行一些过滤，但速度还是更快。
我不是Maven，但我认为我不是把每个酒店都加入到每个术语中，而是先筛选术语，然后选择合适的酒店。

SELECT `hotel`.* FROM `hotel` 
INNER JOIN (
    SELECT `term`.`hotel_ID` FROM `term` 
    INNER JOIN `airport_term` ON airport_term.term_ID=term.ID 
    INNER JOIN `airport` ON airport.ID=airport_term.airport_ID WHERE `airport`.`name` IN ('Vienna', 'Berlin', 'Prague') 
    GROUP BY `term`.`hotel_ID`
) `subquery` ON subquery.hotel_ID=hotel.ID ORDER BY `hotel`.`master_rating` DESC