Sorry, just realized. I want list of users who have such a first edit. Ie if user A makes an upload that's not tagged, the result shouldn't contain user A. 

Anyway, I feel the query should work that way - given it should first compute the subquery, and then limit it to rows having matching entry in change_tag. 


ne 24. 11. 2019 v 21:28 odesílatel Martin Urbanec <> napsal:
Ah, yes, first upload to Commons tagged with that tag. Thanks a lot, John!


ne 24. 11. 2019 v 20:56 odesílatel John <> napsal:
Thats not what that query is getting. that is getting their first upload that is tagged with that change id. If you want to discount any non-upload edits I can look at optimizing it.

On Sun, Nov 24, 2019 at 2:39 PM Martin Urbanec <> wrote:

could someone please help me with optimizing the following query?

USE commonswiki_p;

SELECT first_upload, uploads, username FROM
  SELECT MIN(log_timestamp) AS first_upload, MIN(log_id) AS first_upload_id, COUNT(log_timestamp) AS uploads, log_user_text AS username
  FROM logging_compat
  LEFT JOIN user ON user_id = log_user
  JOIN page ON log_page = page_id
  WHERE log_type = "upload" AND (log_action = "upload" OR log_action = "overwrite") AND user_registration > "20190101000000"
  GROUP BY log_user
) AS first_uploads
JOIN change_tag ON ct_log_id = first_upload_id
WHERE ct_tag_id=21;

It takes over 30 minutes :/. I want to have a list of users whose first contrib to Wikimedia Commons is tagged with tag number 21. 


Wikimedia Cloud Services mailing list (formerly
Wikimedia Cloud Services mailing list (formerly