40273, exploiting GROUP BY keeping first match

Fork of https://lists.wikimedia.org/pipermail/cloud/2019-November/000852.html by Zhuyifei1999
This query is marked as a draft This query has been published by Zhuyifei1999.
SQL

USE commonswiki_p;

SELECT log_timestamp AS first_upload,
    uploads,
    log_user_text AS username
FROM logging_compat
INNER JOIN (
    SELECT log_id AS first_upload_id,
        COUNT(log_timestamp) AS uploads,
        log_actor AS act
    FROM logging_userindex
    WHERE log_type = "upload"
    GROUP BY log_actor
) AS first_uploads
ON log_id = first_upload_id
AND log_id IN (
    SELECT ct_log_id
    FROM change_tag
    WHERE ct_tag_id = 21
);

/*

+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+----------------------+---------+------------------------------------+----------+---------------------------------------------------------------------+
| id   | select_type | table      | type   | possible_keys                                                                                       | key                  | key_len | ref                                | rows     | Extra                                                               |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+----------------------+---------+------------------------------------+----------+---------------------------------------------------------------------+
|    1 | PRIMARY     | change_tag | ref    | change_tag_log_tag_id,change_tag_tag_id_id                                                          | change_tag_tag_id_id | 4       | const                              |   371428 | Using where; Using index                                            |
|    1 | PRIMARY     | logging    | eq_ref | PRIMARY,type_time,log_comment_type,actor_time,log_actor_type_time,log_actor_deleted,log_type_action | PRIMARY              | 4       | commonswiki.change_tag.ct_log_id   |        1 | Using where                                                         |
|    1 | PRIMARY     | comment    | eq_ref | PRIMARY                                                                                             | PRIMARY              | 8       | commonswiki.logging.log_comment_id |        1 | Using index                                                         |
|    1 | PRIMARY     | actor      | eq_ref | PRIMARY                                                                                             | PRIMARY              | 8       | commonswiki.logging.log_actor      |        1 |                                                                     |
|    1 | PRIMARY     | <derived2> | ref    | key0                                                                                                | key0                 | 9       | commonswiki.change_tag.ct_log_id   |       10 | Using where                                                         |
|    2 | DERIVED     | logging    | ref    | type_time,log_type_action                                                                           | type_time            | 34      | const                              | 54236856 | Using index condition; Using where; Using temporary; Using filesort |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+----------------------+---------+------------------------------------+----------+---------------------------------------------------------------------+

Original by Urbanecm:

+------+-------------+------------+--------+---------------------------------------------------------------------------------------------+----------------------+---------+------------------------------------+----------+---------------------------------------------------------------------+
| id   | select_type | table      | type   | possible_keys                                                                               | key                  | key_len | ref                                | rows     | Extra                                                               |
+------+-------------+------------+--------+---------------------------------------------------------------------------------------------+----------------------+---------+------------------------------------+----------+---------------------------------------------------------------------+
|    1 | PRIMARY     | change_tag | ref    | change_tag_log_tag_id,change_tag_tag_id_id                                                  | change_tag_tag_id_id | 4       | const                              |   371428 | Using where; Using index                                            |
|    1 | PRIMARY     | <derived2> | ref    | key0                                                                                        | key0                 | 9       | commonswiki.change_tag.ct_log_id   |       10 | Using where                                                         |
|    2 | DERIVED     | logging    | ref    | type_time,log_comment_type,actor_time,log_actor_type_time,log_actor_deleted,log_type_action | type_time            | 34      | const                              | 50103564 | Using index condition; Using where; Using temporary; Using filesort |
|    2 | DERIVED     | actor      | eq_ref | PRIMARY                                                                                     | PRIMARY              | 8       | commonswiki.logging.log_actor      |        1 |                                                                     |
|    2 | DERIVED     | comment    | eq_ref | PRIMARY                                                                                     | PRIMARY              | 8       | commonswiki.logging.log_comment_id |        1 | Using index                                                         |
|    2 | DERIVED     | user       | eq_ref | PRIMARY                                                                                     | PRIMARY              | 4       | func                               |        1 | Using where                                                         |
+------+-------------+------------+--------+---------------------------------------------------------------------------------------------+----------------------+---------+------------------------------------+----------+---------------------------------------------------------------------+

*/
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
