https://bugzilla.wikimedia.org/show_bug.cgi?id=53577
--- Comment #5 from Sean Pringle <[email protected]> --- Possibilities after talking with Aaron on IRC, assuming group_concat can be sorted in MW code for pg... Using change_tag instead of tag_summary avoids the poor cardinality of ts_log_id and allows better query plans on `logging` indexes: v1 enwiki> explain SELECT log_id,log_type,log_action,log_timestamp,log_user,log_user_text,log_namespace,log_title,log_comment,log_params,log_deleted,user_id,user_name,user_editcount,group_concat(distinct ct_tag separator ' ') as ts_tags FROM `logging` LEFT JOIN `user` ON ((log_user=user_id)) LEFT JOIN change_tag ON (ct_log_id=log_id) WHERE (log_type NOT IN ('suppress','spamblacklist','review')) AND log_user = ? AND ((log_deleted & 4) = 0) GROUP BY log_id ORDER BY log_timestamp DESC LIMIT 51\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: logging type: index possible_keys: type_time,user_time,log_user_type_time key: times key_len: 16 ref: NULL rows: 2583 Extra: Using where; Using temporary *************************** 2. row *************************** id: 1 select_type: SIMPLE table: user type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: change_tag type: ref possible_keys: ct_log_id,change_tag_log_tag key: ct_log_id key_len: 5 ref: enwiki.logging.log_id rows: 35 Extra: Using where; Using index 3 rows in set (0.31 sec) pt-visual-explain JOIN +- Filter with WHERE | +- Index lookup | key change_tag->ct_log_id | possible_keys ct_log_id,change_tag_log_tag | key_len 5 | ref enwiki.logging.log_id | rows 35 +- JOIN +- Bookmark lookup | +- Table | | table user | | possible_keys PRIMARY | +- Constant index lookup | key user->PRIMARY | possible_keys PRIMARY | key_len 4 | ref const | rows 1 +- Table scan +- TEMPORARY table temporary(logging) +- Filter with WHERE +- Bookmark lookup +- Table | table logging | possible_keys type_time,user_time,log_user_type_time +- Index scan key logging->times possible_keys type_time,user_time,log_user_type_time key_len 16 rows 2583 v2 enwiki> explain SELECT log_id,log_type,log_action,log_timestamp,log_user,log_user_text,log_namespace,log_title,log_comment,log_params,log_deleted,user_id,user_name,user_editcount,(select group_concat(distinct ct_tag separator ' ') from change_tag where ct_log_id = log_id) as ts_tags FROM `logging` LEFT JOIN `user` ON ((log_user=user_id)) WHERE (log_type NOT IN ('suppress','spamblacklist','review')) AND log_user = ? AND ((log_deleted & 4) = 0) ORDER BY log_timestamp DESC LIMIT 51\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: logging type: range possible_keys: type_time,user_time,log_user_type_time key: log_user_type_time key_len: 38 ref: NULL rows: 18675 Extra: Using index condition; Using where; Using filesort *************************** 2. row *************************** id: 1 select_type: PRIMARY table: user type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *************************** 3. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: change_tag type: ref possible_keys: ct_log_id,change_tag_log_tag key: ct_log_id key_len: 5 ref: enwiki.logging.log_id rows: 35 Extra: Using where; Using index 3 rows in set (0.32 sec) pt-visual-explain DEPENDENT SUBQUERY +- Filter with WHERE | +- Index lookup | key change_tag->ct_log_id | possible_keys ct_log_id,change_tag_log_tag | key_len 5 | ref enwiki.logging.log_id | rows 35 +- JOIN +- Bookmark lookup | +- Table | | table user | | possible_keys PRIMARY | +- Constant index lookup | key user->PRIMARY | possible_keys PRIMARY | key_len 4 | ref const | rows 1 +- Filesort +- Filter with WHERE +- Index range scan key logging->log_user_type_time possible_keys type_time,user_time,log_user_type_time key_len 38 rows 18675 v2 appears slightly better. -- You are receiving this mail because: You are the assignee for the bug. You are on the CC list for the bug. _______________________________________________ Wikibugs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
