負荷高いSNSのスロークエリログを元にしたSQLチューニング

[kunitada]

手嶋屋ASPの中の、とあるヘビーなSNSにおけるスロークエリ頻出ワースト10
（ユーザー数52000、集計期間：6/15～8/21）


1位 3600件：SELECT c_commu.*, COUNT(c_commu_member.c_member_id) AS count_commu_member FROM c_commu, c_commu_member WHERE N AND
c_commu_member.c_commu_id = c_commu.c_commu_id GROUP BY c_commu_member.c_commu_id ORDER BY count_commu_member DESC LIMIT N, N


2位 373件： SELECT COUNT(*) FROM c_message WHERE c_member_id_to = N AND is_deleted_to = N AND is_send = N LIMIT N, N

3位 262件： SELECT d.c_diary_id, d.subject, d.c_member_id, MAX(dc.r_datetime) AS r_datetime, COUNT(DISTINCT dc.c_diary_comment_id) AS num_comment FROM c_diary AS d INNER JOIN c_diary_comment AS dc USING (c_diary_id), c_diary_comment AS mydc WHERE mydc.c_member_id = N AND mydc.c_diary_id = d.c_diary_id AND mydc.c_member_id <> d.c_member_id AND d.c_member_id NOT IN
(N) AND (d.public_flag = 'S' OR (d.public_flag = 'S' AND d.c_member_id IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N
,N))) GROUP BY dc.c_diary_id ORDER BY r_datetime DESC LIMIT N, N

4位 146件：SELECT * FROM c_diary /*! USE INDEX (r_datetime_c_member_id, r_datetime) */ WHERE c_member_id IN (N) AND public_flag <> 'S' ORDER BY c_diary.r_datetime DESC LIMIT N, N

5位 92件： SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id FROM c_commu_topic as cct, c_commu_topic_comment as cctc /*! USE INDEX (r_datetime_c_commu_id) */ WHERE cct.c_commu_id IN (N, N) AND cctc.c_commu_topic_id = cct.c_commu_topic_id GROUP BY cctc.c_commu_topic_id ORDER BY r_datetime DESC LIMIT N, N

6位 82件： SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id FROM c_commu_topic as cct, c_commu_topic_comment as cctc /*! USE INDEX (r_datetime_c_commu_id) */ WHERE cct.c_commu_id IN (N, N, N, N) AND cctc.c_commu_topic_id = cct.c_commu_topic_id GROUP BY cctc.c_commu_topic_id ORDER BY r_datetime DESC LIMIT N, N

7位 80件： SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id FROM c_commu_topic as cct, c_commu_topic_comment as cctc /*! USE INDEX (r_datetime_c_commu_id) */ WHERE cct.c_commu_id IN (N) AND cctc.c_commu_topic_id = cct.c_commu_topic_id GROUP BY cctc.c_commu_topic_id ORDER BY r_datetime DESC LIMIT N, N

8位 77件： SELECT cct.c_commu_topic_id, cct.c_commu_id, MAX(cctc.r_datetime) as r_datetime, cct.c_member_id FROM c_commu_topic as cct, c_commu_topic_comment as cctc /*! USE INDEX (r_datetime_c_commu_id) */ WHERE cct.c_commu_id IN (N, N, N, N, N, N, N) AND cctc.c_commu_topic_id = cct.c_commu_topic_id GROUP BY cctc.c_commu_topic_id ORDER BY r_datetime DESC LIMIT
N, N

8位 77件： SELECT COUNT(*) FROM c_message WHERE c_member_id_from = N AND is_deleted_from = N AND is_send = N LIMIT N, N

9位 76件： SELECT d.c_diary_id, d.subject, d.c_member_id, MAX(dc.r_datetime) AS r_datetime, COUNT(DISTINCT dc.c_diary_comm
ent_id) AS num_comment FROM c_diary AS d INNER JOIN c_diary_comment AS dc USING (c_diary_id), c_diary_comment AS mydc WHERE mydc.c_member_id = N AND mydc.c_diary_id = d.c_diary_id AND mydc.c_member_id <> d.c_member_id AND d.c_member_id NOT IN
(N,N) AND (d.public_flag = 'S' OR (d.public_flag = 'S' AND d.c_member_id IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N))) GROUP BY dc.c_diary_id ORDER BY r_datetime DESC LIMIT N, N

手嶋屋ASPの中の、別のヘビーなSNSにおけるスロークエリ頻出ワースト10 （ユーザー数24000集計期間：7/19～8/21）

1位 1142件 : SELECT COUNT(*) FROM c_message WHERE c_member_id_to = N AND is_deleted_to = N AND is_send = N LIMIT N, N

2位 404件 ： SELECT c_commu.*, COUNT(c_commu_member.c_member_id) AS count_commu_member FROM c_commu, c_commu_member WHERE N AND c_commu_member.c_commu_id = c_commu.c_commu_id GROUP BY c_commu_member.c_commu_id ORDER BY count_commu_member DESC LIMIT N, N

3位 297件 ： SELECT c_message_id FROM c_message WHERE c_member_id_to = N AND is_deleted_to = N AND is_send = N AND c_message_id > N ORDER BY r_datetime LIMIT N, N

4位 155件 ： SELECT * FROM c_diary /*! USE INDEX (r_datetime_c_member_id, r_datetime) */ WHERE c_member_id IN (N) AND public_flag <> 'S' ORDER BY c_diary.r_datetime DESC LIMIT N, N

5位 76件 ： SELECT c_member_id FROM c_member_profile WHERE c_profile_id = N AND public_flag = 'S' AND c_profile_option_id = N ORDER BY c_member_id DESC

6位 55件 ： SELECT * FROM c_diary /*! USE INDEX (r_datetime_c_member_id, r_datetime) */ WHERE c_member_id IN (N,N) AND public_flag <> 'S' ORDER BY c_diary.r_datetime DESC LIMIT N, N

6位 55件 ：SELECT COUNT(*) FROM c_message WHERE c_member_id_from = N AND is_deleted_from = N AND is_send = N LIMIT N, N

7位 42件 ： SELECT * FROM c_diary /*! USE INDEX (r_datetime_c_member_id, r_datetime) */ WHERE c_member_id IN (N,N,N) AND public_flag <> 'S' ORDER BY c_diary.r_datetime DESC LIMIT N, N

8位 39件 ：SELECT * FROM c_message WHERE c_member_id_to = N AND is_deleted_to = N AND is_send = N ORDER BY r_datetime DESC LIMIT N, N

9位 35件 ： SELECT c_commu.*, COUNT(c_commu_member.c_member_id) AS count_commu_member FROM c_commu, c_commu_member WHERE N AND c_commu.c_commu_category_id = N AND c_commu_member.c_commu_id = c_commu.c_commu_id GROUP BY c_commu_member.c_commu_id ORDER BY count_commu_member DESC LIMIT N, N

-------------
これは別のSNSの集計。
メッセージがかなり活用されてるため、その１と結果が異なる。

関連チケット：#562

​http://openpne.jp/?m=pc&a=page_fh_diary&target_c_diary_id=12435

[ogawa]

関連チケット：#562

[ogawa]

2.12で改善されたSQLが多数含まれます。

再度、スロークエリログを取りなおしてチューニングを進めましょう。