MySQL COUNT(*) 来自相关的 "visits"表

Question

我一直在处理 MySQL 查询，想知道是否有更好的方法来执行此操作(没有相关子查询等...)。有问题的查询是

SELECT 
   (SELECT COUNT(*) as num_visits FROM visit WHERE data_type='gallery'
    and gallery.id=visit.object_id) as num_visits, gallery.id
FROM gallery
    ORDER BY num_visits desc

表结构的相关部分是

gallery
-------
id

visit
-----
id
data_type --> enum('gallery','etc')
object_id --> ties to various tables

如有任何帮助，我们将不胜感激。

Answer 1

   SELECT COUNT(v.object_id) as num_visits, g.id
     FROM gallery g
LEFT JOIN visit v ON g.id=v.object_id AND v.data_type='gallery'
 GROUP BY g.id
 ORDER BY num_visits DESC

但更好的解决方案(从性能角度来看)是预先计算访问量并将其存储在 gallery 表(或其他表)中