Last modified: 2014-05-21 18:42:15 UTC
Executing a query involving the use of ar_user is very slow. Executing SELECT COUNT(*) AS count FROM archive_userindex WHERE `ar_user` ='14836860'; returns: +-------+ | count | +-------+ | 344 | +-------+ 1 row in set (16.76 sec) Executing SELECT COUNT(*) AS count FROM archive_userindex WHERE `ar_user_text` ='Cyberpower678'; returns: +-------+ | count | +-------+ | 344 | +-------+ 1 row in set (0.08 sec) Why not use ar_user_text? I have very good reasons, but it's beside the point.
BUMP. I've opened this almost a month ago with no initial comments. Revision_userindex has a similar problem, but not as severe. Load times are incredibly high at first but speed up if reloaded immediately after. However, if you wait a few hours, load times increase again.
For clarity I'm not trying to use rev_user_text, or ar_user_text.
Esisting Indeces on revision table: PRIMARY KEY (`rev_page`,`rev_id`), UNIQUE KEY `rev_id` (`rev_id`), KEY `rev_timestamp` (`rev_timestamp`), KEY `page_timestamp` (`rev_page`,`rev_timestamp`), KEY `user_timestamp` (`rev_user`,`rev_timestamp`), KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`,`rev_user`,`rev_deleted`,`rev_minor_edit`,`rev_text_id`,`rev_comment`) Existing Indeces on archive table: PRIMARY KEY (`ar_id`), KEY `name_title_timestamp` (`ar_namespace`,`ar_title`,`ar_timestamp`), KEY `usertext_timestamp` (`ar_user_text`,`ar_timestamp`), KEY `ar_revid` (`ar_rev_id`) => please provide additional index on archive table KEY `user_timestamp` (`ar_user`,`ar_timestamp`) by analogy to revision table.
s/Indeces/Indices/
Additional explanation on this (as Cyberpower678 pointed out) `rev_user_text` && `ar_user_text` may give wrong results, if a user has been renamed
Adding the index should be OK.
A user_timestamp index has been added the archive base tables on labdb100[123] for all wikis.
(In reply to Sean Pringle from comment #7) > A user_timestamp index has been added the archive base tables on > labdb100[123] for all wikis. Awesome sauce. Now I just need to wait for the new internet to arrive at my new house, and I can make the changes.
(In reply to Sean Pringle from comment #7) > A user_timestamp index has been added the archive base tables on > labdb100[123] for all wikis. Thanks for the fix, but I think revision might still have some performance issues. [time] => 153.57 [query] => SELECT rev_timestamp, page_title, page_namespace FROM revision_userindex JOIN page ON page_id = rev_page WHERE `rev_user` ='82835' AND `rev_timestamp` > 1 ORDER BY rev_timestamp ASC LIMIT 0,2698492; [result] => succeeded was returned by my tool in a debug report.
Take note that the time field is in seconds it took to execute the query.
