MySQL优化

发布于 2024-12-04 16:27:48 字数 656 浏览 0 评论 0原文

我试图加快我的网站速度，主要问题是一个几乎在所有页面上运行的查询（下面的代码）。 LIMIT 更改可以加快速度，但我需要使用 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

原文

I trying to speed up my site and main problem on it one query (code below) that runs on almost all pages.
LIMIT changes make speed up, but I need to use LIMIT 99 :(
On 24 speed is ~ 0.06 sec.
On 100 ~ 0.105 sec.
Server restriction: subquery (SELECT video_names.name...) can't be changed to 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风向决定发型 2024-12-11 16:27:48

那行不通吗？

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
;

Couldn't that work ?

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
;

回复收藏 0 原文

烙印 2024-12-11 16:27:48

试试这个脚本

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;

Try this script

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;

回复收藏 0 原文