Last modified: 2013-10-28 17:17:00 UTC
I'm currently trying to port one of my Toolserver tools and ran into a problem with DB queries taking orders of magnitude longer on Tools than the TS. The problem appears to be in queries of the type: SELECT rc_timestamp FROM recentchanges WHERE rc_user_text='$username' ORDER BY rc_timestamp DESC LIMIT 1 On the Toolserver: mysql> SELECT rc_timestamp FROM recentchanges WHERE rc_user_text='MaxSem' ORDER BY rc_timestamp DESC LIMIT 1; +----------------+ | rc_timestamp | +----------------+ | 20131009151959 | +----------------+ 1 row in set (0.02 sec) On Tools: MariaDB [enwiki_p]> SELECT rc_timestamp FROM recentchanges WHERE rc_user_text='MaxSem' ORDER BY rc_timestamp DESC LIMIT 1; +----------------+ | rc_timestamp | +----------------+ | 20131009151959 | +----------------+ 1 row in set (7.38 sec) Using rc_user instead makes little difference. The farther in the past the most recent rc_timestamp is, the worse the performance (users with no edits in RC take 10+ seconds). So I'm assuming the problem is an extra index that exists on TS, but not on the Labs replicas.
There is no index on rc_user and rc_user_text because of supression; but I've just added a recentchanges_userindex view that does for this purpose. It's in the process of being deployed, but that may take a few hours due to the necessary table locks.