强制索引解决mysql orderby查询效率慢的问题
SELECT
v.*,
FROM
small_videos v
<!-- 增加强制索引 -->
FORCE INDEX(INDEX_CREATED_AT)
LEFT JOIN cl_user_videos cv ON v.id = cv.video_id
AND cv.video_type = 20
AND cv.user_id = 15
LEFT JOIN (
SELECT
video_id,
count(video_id) AS loveCnt
FROM
cl_user_videos
WHERE
video_type = 20
AND love = '1'
GROUP BY
video_id
) cl ON v.id = cl.video_id
LEFT JOIN (
SELECT
video_id,
watch_count AS watchCnt
FROM
videos_report
WHERE
video_type = 20
) cr ON v.id = cr.video_id
WHERE
v.bsy_url != ''
AND v.bsy_img_url != ''
AND (
cv.interest != 1
OR cv.interest IS NULL
)
AND v.state = 1
ORDER BY
v.created_at DESC
LIMIT 6