Jak zoptymalizować zapytanie, jeśli tabela zawiera 10000 wpisów przy użyciu MySQL?
Kiedy wykonuję to zapytanie w ten sposób, zajmują one tyle czasu na wykonanie, ponieważ tabela user_fans zawieradziesięć tysięcy wpisy użytkowników. Jak mogę to zoptymalizować?
Pytanie
SELECT uf.`user_name`,uf.`user_id`,
@post := (SELECT COUNT(*) FROM post WHERE user_id = uf.`user_id`) AS post,
@post_comment_likes := (SELECT COUNT(*) FROM post_comment_likes WHERE user_id = uf.`user_id`) AS post_comment_likes,
@post_comments := (SELECT COUNT(*) FROM post_comments WHERE user_id = uf.`user_id`) AS post_comments,
@post_likes := (SELECT COUNT(*) FROM post_likes WHERE user_id = uf.`user_id`) AS post_likes,
(@post+@post_comments) AS `sum_post`,
(@post_likes+@post_comment_likes) AS `sum_like`,
((@post+@post_comments)*10) AS `post_cal`,
((@post_likes+@post_comment_likes)*5) AS `like_cal`,
((@post*10)+(@post_comments*10)+(@post_likes*5)+(@post_comment_likes*5)) AS `total`
FROM `user_fans` uf ORDER BY `total` DESC lIMIT 20