Page MenuHomePhabricator

ActiveUsersPager query expensive when specifying a starting rc_user_text value
Closed, ResolvedPublic


ActiveUsersPager generates a query like this when a user name is supplied as a starting value:

SELECT /* IndexPager::buildQueryInfo (ActiveUsersPager) */ rc_user_text AS user_name, rc_user_text, MAX(rc_user) AS user_id, COUNT(*) AS recentedits FROM recentchanges FORCE INDEX (rc_user_text) WHERE (rc_user > 0) AND (rc_log_type IS NULL OR rc_log_type != 'newusers') AND (rc_timestamp >= '?') AND (NOT EXISTS (SELECT 1 FROM ipblocks WHERE (rc_user=ipb_user) AND ipb_deleted = '1' )) AND (rc_user_text >= '?') GROUP BY rc_user_text ORDER BY rc_user_text LIMIT 101;

Without the rc_user_text >= '?' clause the query executes in seconds. With the clause it takes minutes. Removing the FORCE INDEX doesn't help.

EXPLAIN from an enwiki slave:

  • 1. row ******* id: 1 select_type: PRIMARY table: recentchanges type: range

possible_keys: rc_user_text

    key: rc_user_text
key_len: 273
    ref: NULL
   rows: 4013039  <-- eek!
  Extra: Using index condition; Using where
  • 2. row ******* id: 2 select_type: DEPENDENT SUBQUERY table: ipblocks type: ref

possible_keys: ipb_user

    key: ipb_user
key_len: 4
    ref: enwiki.recentchanges.rc_user
   rows: 1
  Extra: Using where

2 rows in set (0.25 sec)

The functionality to specify a starting rc_user_text value should be disabled, or the page redesigned.

Version: 1.23.0
Severity: normal
See Also:



Event Timeline

bzimport raised the priority of this task from to High.Nov 22 2014, 2:42 AM
bzimport set Reference to bz59988.
bzimport added a subscriber: Unknown Object (MLST).

Slight correction: even without the clause the query shows up in the slow logs sometimes. It switches to an index scan on rc_user_text and is quite slow as LIMIT increases (eg, 101 to 501).