Ladsgroup added a comment.
"Using index condition; Using where; Using filesort" that's sad and slow but
it works because the range is small.
but I agree timestamp index does make sense for most wikis but not wikidata.
The query I got from tendril is:
SELECT /* SpecialRecentChanges::doMainQuery */ rc_id, rc_timestamp,
rc_namespace, rc_title, rc_minor, rc_bot, rc_new, rc_cur_id, rc_this_oldid,
rc_last_oldid, rc_type, rc_source, rc_patrolled, rc_ip, rc_old_len, rc_new_len,
rc_deleted, rc_logid, rc_log_type, rc_log_action, rc_params,
comment_rc_comment.comment_text AS `rc_comment_text`,
comment_rc_comment.comment_data AS `rc_comment_data`,
comment_rc_comment.comment_id AS `rc_comment_cid`, actor_rc_user.actor_user AS
`rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor, wl_user,
wl_notificationtimestamp, page_latest, (SELECT GROUP_CONCAT(ctd_name SEPARATOR
', ') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE
ct_rc_id=rc_id ) AS `ts_tags`, ores_damaging_cls.oresc_probability AS
`ores_damaging_score`, ores_goodfaith_cls.oresc_probability AS
`ores_goodfaith_score` FROM `recentchanges` JOIN `comment` `comment_rc_comment`
ON ((comment_rc_comment.comment_id = rc_comment_id)) JOIN `actor`
`actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) LEFT JOIN `watchlist`
ON (wl_user = 28859 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace))
LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `ores_classification`
`ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 11 AND
(ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class =
1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON
(ores_goodfaith_cls.oresc_model = 12 AND
(ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class
= 1) WHERE rc_bot = 0 AND ((rc_this_oldid = page_latest) OR rc_type IN (3, 142)
) AND (rc_type != 6) AND rc_patrolled = 0 AND (rc_namespace IN ('12', '120',
'146', '640', '828')) AND (rc_timestamp >= '20191216180112') AND (rc_source !=
'wb') AND rc_new IN (0, 1) ORDER BY rc_timestamp DESC LIMIT 500;
The explain for that is:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: recentchanges
type: range
possible_keys:
rc_timestamp,new_name_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid
key: rc_timestamp
key_len: 16
ref: NULL
rows: 10704405
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: watchlist
type: eq_ref
possible_keys: wl_user,namespace_title,wl_user_notificationtimestamp
key: wl_user
key_len: 265
ref:
const,wikidatawiki.recentchanges.rc_namespace,wikidatawiki.recentchanges.rc_title
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: actor_rc_user
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: wikidatawiki.recentchanges.rc_actor
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: page
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: wikidatawiki.recentchanges.rc_cur_id
rows: 1
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: PRIMARY
table: ores_damaging_cls
type: eq_ref
possible_keys: oresc_rev_model_class
key: oresc_rev_model_class
key_len: 7
ref: wikidatawiki.recentchanges.rc_this_oldid,const,const
rows: 1
Extra:
*************************** 6. row ***************************
id: 1
select_type: PRIMARY
table: ores_goodfaith_cls
type: eq_ref
possible_keys: oresc_rev_model_class
key: oresc_rev_model_class
key_len: 7
ref: wikidatawiki.recentchanges.rc_this_oldid,const,const
rows: 1
Extra:
*************************** 7. row ***************************
id: 1
select_type: PRIMARY
table: comment_rc_comment
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: wikidatawiki.recentchanges.rc_comment_id
rows: 1
Extra:
*************************** 8. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: change_tag
type: ref
possible_keys: change_tag_rc_tag_id,change_tag_tag_id_id
key: change_tag_rc_tag_id
key_len: 5
ref: wikidatawiki.recentchanges.rc_id
rows: 1
Extra: Using index
*************************** 9. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: change_tag_def
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: wikidatawiki.change_tag.ct_tag_id
rows: 1
Extra:
9 rows in set (0.00 sec)
`
TASK DETAIL
https://phabricator.wikimedia.org/T239192
EMAIL PREFERENCES
https://phabricator.wikimedia.org/settings/panel/emailpreferences/
To: Ladsgroup
Cc: Ladsgroup, Addshore, Umherirrender, DannyS712, Aklapper, darthmon_wmde,
Nandana, kostajh, Jony, Lahi, Gq86, Darkminds3113, Pablo-WMDE,
GoranSMilovanovic, QZanden, LawExplorer, Vali.matei, _jensen, rosalieper,
Taiwania_Justo, Scott_WUaS, Jonas, Volker_E, Wikidata-bugs, aude, GWicke,
Dinoguy1000, Lydia_Pintscher, Jdforrester-WMF, Mbch331, Rxy, Jay8g, Krenair
_______________________________________________
Wikidata-bugs mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs
