缓慢的MySQL查询，按限制排序
我目前加入5个表来选择20个对象来显示用户，不幸的是，如果我使用GROUP BY和ORDER BY它会变得非常慢。
示例查询看起来像这样：
SELECT r.name, l.name, o.typ, o.id, persons, children, description, rating, totalratings, minprice, picture FROM angebote as a
JOIN objekte as o ON a.fid_objekt = o.id
JOIN regionen as r ON a.fid_region = r.id
JOIN laender as l ON a.fid_land = l.id
WHERE l.slug="aegypten" AND a.letztes_angebot >= 1
GROUP BY a.fid_objekt ORDER BY rating DESC LIMIT 0,20
查询的解释显示了这一点：
+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+
| 1 | SIMPLE | l | ref | PRIMARY,slug | slug | 767 | const | 1 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | o | ALL | PRIMARY | NULL | NULL | NULL | 186779 | Using join buffer (flat, BNL join) |
| 1 | SIMPLE | a | ref | unique_key,letztes_angebot | unique_key | 8 | ferienhaeuser.o.id,ferienhaeuser.l.id | 1 | Using where |
| 1 | SIMPLE | r | eq_ref | PRIMARY | PRIMARY | 4 | ferienhaeuser.a.fid_region | 1 | |
+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+
所以它看起来并不使用表格对象的objekte ，分析表明它使用2.7s来拷贝到tmp表格。
代替FROM angebote或JOIN objekte我试着用(SELECT * GROUP BY id)但不幸的是这并没有改进。
用于WHERE ， ORDER BY和GROUP BY的字段也被编入索引。
我想我错过了这里的一些基本概念，任何帮助将不胜感激。
由于我很可能在表格中犯了一个错误，这里是对它们的描述：
Objekte
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | objekte | CREATE TABLE `objekte` ( `id` int(11) NOT NULL, `typ` varchar(50) NOT NULL, `persons` int(11) NOT NULL, `children` int(11) NOT NULL, `description` text NOT NULL, `rating` float NOT NULL, `totalratings` int(11) NOT NULL, `minprice` float NOT NULL, `picture` varchar(255) NOT NULL, `last_offer` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `minprice` (`minprice`), KEY `rating` (`rating`), KEY `last_offer` (`last_offer`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Angebote
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| angebote | CREATE TABLE `angebote` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fid_objekt` int(11) NOT NULL,
`fid_land` int(11) NOT NULL,
`fid_region` int(11) NOT NULL,
`fid_subregion` int(11) NOT NULL,
`letztes_angebot` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_key` (`fid_objekt`,`fid_land`,`fid_region`,`fid_subregion`),
KEY `letztes_angebot` (`letztes_angebot`),
KEY `fid_objekt` (`fid_objekt`),
KEY `fid_land` (`fid_land`),
KEY `fid_region` (`fid_region`),
KEY `fid_subregion` (`fid_subregion`)
) ENGINE=InnoDB AUTO_INCREMENT=2433073 DEFAULT CHARSET=utf8 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
laender，regionen，subregionen（相同结构）
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | laender | CREATE TABLE `laender` ( `id` int(11) NOT NULL AUTO_INCREMENT, `iso` varchar(2) NOT NULL, `name` varchar(255) NOT NULL, `slug` varchar(255) NOT NULL, `letztes_angebot` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `iso` (`iso`), KEY `slug` (`slug`), KEY `letztes_angebot` (`letztes_angebot`) ) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8 | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
首先这是一个非标准组。 因此，当你升级到MySQL 5.7时，它将停止工作。
最大的问题来自于objekte表上没有使用索引的事实。 更糟糕的是，您在该表上的评级字段上订购，但索引仍未被使用。 一个可能的解决方案是创建一个像这样的复合索引：
CREATE INDEX objekte_idx ON objekte(id,rating);
这里不需要使用GROUP BY。 您没有使用聚合函数。 所以从查询中删除GROUP BY。 删除群组将提高查询性能。 也不需要定义0作为限制。
SELECT r.name, l.name, o.typ, o.id, persons, children, description, rating, totalratings, minprice, picture FROM angebote as a
JOIN objekte as o ON a.fid_objekt = o.id
JOIN regionen as r ON a.fid_region = r.id
JOIN laender as l ON a.fid_land = l.id
WHERE l.slug="aegypten" AND a.letztes_angebot >= 1
ORDER BY rating DESC LIMIT 20