Page MenuHomePhabricator

Use index for CompareService::getTotalEditsFromIp and remove unused count
Closed, ResolvedPublic1 Estimated Story Points

Description

As discussed in T248588#6131426, we can speed up this query by changing it from:

SELECT COUNT(*) AS `total_edits`,COUNT(distinct cuc_user_text) AS `total_users` FROM `cu_changes` WHERE cuc_ip = [ip] AND cuc_type IN (0,1) LIMIT 1;

to:

SELECT COUNT(*) AS `total_edits` FROM `cu_changes` WHERE cuc_ip_hex = [ip] AND cuc_type IN (0,1) LIMIT 1;

This makes use of the cuc_ip_hex_time index and removes the COUNT(distinct cuc_user_text), which is unused in the code.

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 13 2020, 10:01 AM
Niharika triaged this task as Medium priority.May 13 2020, 3:56 PM
Niharika moved this task from Untriaged to Cards ready to be discussed on the Anti-Harassment board.
ARamirez_WMF set the point value for this task to 1.May 13 2020, 4:18 PM

Change 601348 had a related patch set uploaded (by Tchanders; owner: Tchanders):
[mediawiki/extensions/CheckUser@master] CompareService: Use index for getTotalEditsFromIp query

https://gerrit.wikimedia.org/r/601348

Change 601348 merged by jenkins-bot:
[mediawiki/extensions/CheckUser@master] CompareService: Use index for getTotalEditsFromIp query

https://gerrit.wikimedia.org/r/601348

dom_walden added a subscriber: dom_walden.

The change is minor, and has no external effects.

However, just in case, I briefly retested the total edit counts per IP.

The query that is run now is of the form: SELECT COUNT(*) AS 'total_edits' FROM 'cu_changes' WHERE cuc_ip_hex = 'C0A879B4' AND cuc_type IN (0,1) LIMIT 1, which is consistent with the expected outcome in the description.

Niharika closed this task as Resolved.Jul 31 2020, 9:43 PM