手嶋屋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の集計。
メッセージがかなり活用されてるため、その1と結果が異なる。
関連チケット:#562
http://openpne.jp/?m=pc&a=page_fh_diary&target_c_diary_id=12435
関連チケット:#562