假设我有 2 个表。一个包含汽车制造商的名称及其ID,第二个包含有关汽车型号的信息。我需要从第一个表中选择其中的几个,但要按第二个表中链接的数量对它们进行排序。
目前,我的查询如下所示:
SELECT DISTINCT `manufacturers`.`name`,
`manufacturers`.`cars_link`,
`manufacturers`.`slug`
FROM `manufacturers`
JOIN `cars`
ON manufacturers.cars_link = cars.manufacturer
WHERE ( NOT ( `manufacturers`.`cars_link` IS NULL ) )
AND ( `cars`.`class` = 'sedan' )
ORDER BY (SELECT Count(*)
FROM `cars`
WHERE `manufacturers`.cars_link = `cars`.manufacturer) DESC
对于我的踏板车来说,它工作正常,大小只有几十 mb。但是现在我需要对汽车表做同样的事情,它的大小只有几百兆字节。问题是查询需要很长时间,有时甚至会导致nginx超时。另外,我认为我拥有所有必要的数据库索引。上面的查询还有其他选择吗?
让我们尝试使用子查询来代替您的计数。
select * from (
select distinct m.name, m.cars_link, m.slug
from manufacturers m
join cars c on m.cars_link=c.manufacturer
left join
(select count(1) ct, c1.manufacturer from manufacturers m1
inner join cars_link c2 on m1.cars_link=c2.manufacturer
where coalesce(m1.cars_link, '') != '' and c1.class='sedan'
group by c1.manufacturer) as t1
on t1.manufacturer = c.manufacturer
where coalesce(m.cars_link, '') != '' and c.class='sedan') t2
order by t1.ct