Skip to content

Instantly share code, notes, and snippets.

@terrorobe
Last active January 11, 2018 19:54
Show Gist options
  • Save terrorobe/c010c95eda49a57e09602398c673750d to your computer and use it in GitHub Desktop.
Save terrorobe/c010c95eda49a57e09602398c673750d to your computer and use it in GitHub Desktop.
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)
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)
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment