mysql - 优化mysql镜像数量和随机数

Question

如果有人可以帮助我，那就太好了。

基本上，图像属于相册。

我正在运行的查询非常慢，我可以提供一些优化建议。

查询的缓慢部分是 (SELECT count(*) from album_image where album_image.album_id = al.id) as image_count 所以任何关于优化的建议都会很棒。我已经使用 Expanded 来找到这里的问题。我需要选择一个随机 guid id，它是每个相册中的随机图像，因此是内部连接。我读到使用 RAND() 不是很好，但我不确定这是否是从每个相册中选择随机图像的最佳方式

SELECT al.id, al.alias_title, al.title, al.hits, al.created_on, uf.real_name, uf.user_name, T.guid_id,
(SELECT count(*) from album_image where album_image.album_id = al.id) as image_count
FROM album al 
INNER JOIN (SELECT imx.id, aix.album_id, imx.guid_id FROM image imx 
                INNER JOIN album_image aix ON imx.id = aix.image_id 
                ORDER BY floor(RAND()*(SELECT count(1) FROM image))) AS T ON al.id = T.album_id             
INNER JOIN user us ON al.user_id = us.id
LEFT JOIN user_flickr uf ON us.flickr_id = uf.id
LEFT JOIN user_site uss ON us.user_site_id= uss.id
WHERE al.approved = 'Yes' AND al.visible ='1'
GROUP BY T.album_id;

Answer 1

这部分查询速度很慢，因为它对返回的每一行都执行一次。您需要将逻辑移至 from 子句中:

SELECT al.id, al.alias_title, al.title, al.hits, al.created_on, uf.real_name, 
       uf.user_name, T.guid_id,
       ai.cnt as image_count
FROM album al inner join
     (SELECT imx.id, aix.album_id, imx.guid_id
      FROM image imx inner join
           album_image aix
      ON imx.id = aix.image_id 
      ORDER BY floor(RAND()*(SELECT count(1) FROM image))
     ) AS 
     ON al.id = T.album_id INNER JOIN
     user us
     ON al.user_id = us.id left join
     user_flickr uf
     ON us.flickr_id = uf.id left join
     user_site uss
     ON us.user_site_id= uss.id left join
     (select album_id, count(*) as cnt
      from album_image ai
      group by album_id
     ) ai
     on ai.album_id = al.id
WHERE al.approved = 'Yes' AND al.visible ='1'
GROUP BY T.album_id;