MySQL优化

Question

我试图加快我的网站和它的主要问题，一个查询(下面的代码)，几乎在所有页面上运行。更改限制会加快速度，但我需要使用LIMIT 99 :(在24秒时速度约为0.06秒。在100 ~ 0.105秒内。服务器限制:子查询(SELECT video_names.name...)无法更改为JOIN

SELECT `videos`.`name`, `videos`.`episodes`, `videos`.`is_ended`, `videos`.`id`, `videos`.`logo`, `videos`.`type`, `user_videos`.`episode`, 
(SELECT `video_names`.`name` 
 FROM (`video_names`) 
 WHERE `video_names`.`vid_id` = `videos`.`id` LIMIT 1) as alt_name
FROM (`videos`)
JOIN `user_videos` ON `user_videos`.`vid_id` = `videos`.`id`
WHERE `user_videos`.`user_id` = '1'
AND `user_videos`.`status` = '1'
GROUP BY `videos`.`id`
ORDER BY `last_change` desc
LIMIT 24  

Answer 1

我测试来自StackOverflow和其他来源的所有变体。最高速度：

1. Query from Query (time)
2. Answer "Couldn't that work" (time x2)
3. Queries without sql_big_selects=1 (time X4.5)
4. 使用sql_big_selects=1 (time x5和更慢的版本)查询

Answer 2

这不能行得通吗？

SELECT
  `videos`.`name`
, `videos`.`episodes`
, `videos`.`is_ended`
, `videos`.`id`
, `videos`.`logo`
, `videos`.`type`
, `user_videos`.`episode`
, `video_names`.`name`
FROM `videos`
, `user_videos`
, `video_names`
WHERE TRUE
  AND `user_videos`.`user_id` = '1'
  AND `user_videos`.`status` = '1'
  AND `videos`.`id` = `user_videos`.`vid_id`
  AND `videos`.`id` = `video_names`.`vid_id`
GROUP BY `videos`.`id`
ORDER BY `last_change` DESC
LIMIT 24
;

Answer 3

尝试此脚本

SELECT
    `v`.`name`
    , `v`.`episodes`
    , `v`.`is_ended`
    , `v`.`id`
    , `v`.`logo`
    , `v`.`type`
    , `u`.`episode`
    , `v`.`alt_name`
FROM `user_videos` `u`
JOIN (
    SELECT `videos`.*, `video_names`.`name` AS alt_name FROM `videos`
    JOIN `video_names` ON `video_names`.`vid_id` = `videos`.`id`
    GROUP BY `videos`.`id`
) `v` ON `u`.`vid_id` = `c`.`id`
WHERE `u`.`user_id` = '1'
AND `u`.`status` = '1'
ORDER BY `last_change` DESC
LIMIT 24;