我有一个特定的MySQL查询,它很慢,我不知道为什么。
SELECT
s.title,
p.minPrice,
s.booking, r.url
FROM shows s
INNER JOIN showResources r
ON r.showID = s.id
INNER JOIN performances p
ON p.showID = s.id
WHERE s.lastDate >= CURDATE()
AND r.type = 'rectangle-poster'
AND p.minPrice > 0
GROUP BY s.id
ORDER BY p.minPrice ASC
LIMIT 30
此查询的说明如下所示:
select_type table type possible_keys key key_len ref rows extra
1 SIMPLE s range PRIMARY,lastDate lastDate 4 NULL 291 Using index condition; Using temporary; Using filesort
1 SIMPLE r ref showID,type showID 5 thistle.s.id 1 Using where
1 SIMPLE p ref showID,minPrice showID 5 thistle.s.id 1 Using where
在同一台服务器上,其他看似更复杂的查询速度非常快 - 但这个通常需要大约 4 秒才能运行,我只是不知道为什么。我什至删除了表并重新创建它们,以防万一它是一些奇怪的损坏,但没有运气。MySQL专家可以告诉我我在这里做错了什么吗?
试试这个:
SELECT
s.id AS id,
s.title,
p.minPrice AS min_price,
s.booking,
r.url
FROM shows s
INNER JOIN showResources r
ON r.showID = s.id AND s.lastDate >= CURDATE() AND r.type = 'rectangle-poster'
INNER JOIN performances p
ON p.showID = s.id AND p.minPrice > 0
GROUP BY id
ORDER BY min_price ASC
LIMIT 30