子テーブルの値の合計・平均を親テーブルに格納したい時は、子テーブルをサブクエリ化して、親テーブルにleftjoinする(ただしリアルタイムではなくなる)

ユーザ数や投稿数が何十万件もあるので、集計済みの値をユーザのカラムに格納して検索スピードを上げたい！
例えば、ユーザ毎の投稿の評価(スターの数)の平均を格納したい時など

相関サブクエリだと激遅だったので、leftjoinとサブクエリで実装してみた

UPDATE 
  users 
  LEFT JOIN (
    SELECT 
      user_id, 
      AVG(
        IFNULL(posts.star, 0)
      ) as post_star 
    FROM 
      posts 
    GROUP BY 
      posts.user_id
  ) AS avg_posts -- ユーザ毎の平均スター数
  ON users.id = avg_posts.user_id 
SET 
  users.avg_star = IFNULL(avg_posts.star, 0)

UPDATE

users

LEFT JOIN (

SELECT

user_id,

AVG(

IFNULL(posts.star, 0)

) as post_star

FROM

posts

GROUP BY

posts.user_id

) AS avg_posts -- ユーザ毎の平均スター数

ON users.id = avg_posts.user_id

SET

users.avg_star = IFNULL(avg_posts.star, 0)

夜間バッチで実行して、usersカラムを更新すると、select文(検索)が単純＆早くなる。
※ただしリアルタイムではなくなる

select
	user.name,
	user.avg_star
from
	users
order by
	user.avg_star desc

select

user.name,

user.avg_star

from

users

order by

user.avg_star desc

関連記事