1_original_query.txt

mysql> explain SELECT sub.id, sub.oid AS oid, COUNT(sr.host) AS copies
    ->         FROM (
    ->           SELECT id, oid FROM storage_blobs
    ->           WHERE id BETWEEN 0 AND 1256276
    ->         ) AS sub
    ->         LEFT JOIN (
    ->           SELECT fs.host, fs.non_voting, sr.storage_blob_id
    ->           FROM storage_replicas AS sr
    ->           INNER JOIN storage_file_servers fs
    ->             ON sr.host = fs.host AND fs.non_voting = 1 AND fs.online = 1
    ->         ) AS sr
    ->         ON sub.id = sr.storage_blob_id
    ->         GROUP BY sub.id
    ->         HAVING copies != 1
    ->         ORDER BY id
    ->         LIMIT 5000
    -> ;
+----+-------------+---------------+------------+--------+----------------------------------------------------------+----------------+---------+--------------------------------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table         | partitions | type   | possible_keys                                            | key            | key_len | ref                                                          | rows | filtered | Extra                                        |
+----+-------------+---------------+------------+--------+----------------------------------------------------------+----------------+---------+--------------------------------------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | storage_blobs | NULL       | range  | PRIMARY,index_storage_blobs_on_oid                       | PRIMARY        | 4       | NULL                                                         |    6 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | fs            | NULL       | ALL    | index_storage_file_servers_on_host                       | NULL           | NULL    | NULL                                                         |    3 |   100.00 | Using where                                  |
|  1 | SIMPLE      | sr            | NULL       | eq_ref | by_server_blob,index_storage_replicas_on_storage_blob_id | by_server_blob | 771     | github_enterprise.fs.host,github_enterprise.storage_blobs.id |    1 |   100.00 | Using index                                  |
+----+-------------+---------------+------------+--------+----------------------------------------------------------+----------------+---------+--------------------------------------------------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

2_modified_group_by_oid

mysql> explain SELECT sb.id, sb.oid, COUNT(sr.host) AS copies
    -> FROM storage_blobs sb
    -> LEFT JOIN storage_replicas sr ON (sb.id = sr.storage_blob_id AND sr.host IN ('storage-server-9855242c-b95c-11e6-8aab-005056852f7f'))
    -> WHERE sb.id BETWEEN 0 AND 1256276
    -> GROUP BY sb.oid
    -> HAVING copies != 1
    -> ORDER BY sb.id
    -> LIMIT 5000;
+----+-------------+-------+------------+--------+----------------------------------------------------------+----------------+---------+-------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                                            | key            | key_len | ref                           | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+----------------------------------------------------------+----------------+---------+-------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | sb    | NULL       | range  | PRIMARY,index_storage_blobs_on_oid                       | PRIMARY        | 4       | NULL                          |    6 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | sr    | NULL       | eq_ref | by_server_blob,index_storage_replicas_on_storage_blob_id | by_server_blob | 771     | const,github_enterprise.sb.id |    1 |   100.00 | Using index                                  |
+----+-------------+-------+------------+--------+----------------------------------------------------------+----------------+---------+-------------------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

3_modified_group_by_id

mysql> explain SELECT sb.id, sb.oid, COUNT(sr.host) AS copies
    -> FROM storage_blobs sb
    -> LEFT JOIN storage_replicas sr ON (sb.id = sr.storage_blob_id AND sr.host IN ('storage-server-9855242c-b95c-11e6-8aab-005056852f7f'))
    -> WHERE sb.id BETWEEN 0 AND 1256276
    -> GROUP BY sb.id
    -> HAVING copies != 1
    -> ORDER BY sb.id
    -> LIMIT 5000;
+----+-------------+-------+------------+--------+----------------------------------------------------------+----------------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys                                            | key            | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+----------------------------------------------------------+----------------+---------+-------------------------------+------+----------+-------------+
|  1 | SIMPLE      | sb    | NULL       | range  | PRIMARY,index_storage_blobs_on_oid                       | PRIMARY        | 4       | NULL                          |    6 |   100.00 | Using where |
|  1 | SIMPLE      | sr    | NULL       | eq_ref | by_server_blob,index_storage_replicas_on_storage_blob_id | by_server_blob | 771     | const,github_enterprise.sb.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+----------------------------------------------------------+----------------+---------+-------------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)