Ah, yes, first *upload* to Commons tagged with that tag. Thanks a lot, John!
Martin ne 24. 11. 2019 v 20:56 odesÃlatel John <[email protected]> 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 < > [email protected]> wrote: > >> Hello, >> >> 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. >> >> Thanks! >> >> Martin >> _______________________________________________ >> Wikimedia Cloud Services mailing list >> [email protected] (formerly [email protected]) >> https://lists.wikimedia.org/mailman/listinfo/cloud > > _______________________________________________ > Wikimedia Cloud Services mailing list > [email protected] (formerly [email protected]) > https://lists.wikimedia.org/mailman/listinfo/cloud
_______________________________________________ Wikimedia Cloud Services mailing list [email protected] (formerly [email protected]) https://lists.wikimedia.org/mailman/listinfo/cloud
