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
<code>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 </code>