Skip to content

Instantly share code, notes, and snippets.

@dbolser-ebi

dbolser-ebi/gist:cdb78cf3859fc68118efc87fa1e41ec4

Last active October 30, 2018 11:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dbolser-ebi/cdb78cf3859fc68118efc87fa1e41ec4 to your computer and use it in GitHub Desktop.
Save dbolser-ebi/cdb78cf3859fc68118efc87fa1e41ec4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
EXPLAIN SELECT COUNT(*) FROM tmp_sample_genotype_single_bp;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
EXPLAIN SELECT COUNT(DISTINCT variation_id) FROM tmp_sample_genotype_single_bp;
+----+-------------+-------------------------------+-------+---------------+---------------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------+-------+---------------+---------------+---------+------+----------+--------------------------+
| 1 | SIMPLE | tmp_sample_genotype_single_bp | range | variation_idx | variation_idx | 4 | NULL | 69386320 | Using index for group-by |
+----+-------------+-------------------------------+-------+---------------+---------------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)
EXPLAIN SELECT COUNT(DISTINCT variation_id), COUNT(*) FROM tmp_sample_genotype_single_bp;
+----+-------------+-------------------------------+-------+---------------+---------------+---------+------+-----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------+-------+---------------+---------------+---------+------+-----------+-------------+
| 1 | SIMPLE | tmp_sample_genotype_single_bp | index | NULL | variation_idx | 4 | NULL | 277545278 | Using index |
+----+-------------+-------------------------------+-------+---------------+---------------+---------+------+-----------+-------------+
1 row in set (0.00 sec)
EXPLAIN SELECT COUNT(*) FROM tmp_sample_genotype_single_bp INNER JOIN variation USING (variation_id);
+----+-------------+-------------------------------+-------+---------------+---------------+---------+-----------------------------------------------------------------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------+-------+---------------+---------------+---------+-----------------------------------------------------------------+----------+--------------------------+
| 1 | SIMPLE | variation | index | PRIMARY | PRIMARY | 4 | NULL | 71156260 | Using index |
| 1 | SIMPLE | tmp_sample_genotype_single_bp | ref | variation_idx | variation_idx | 4 | solanum_lycopersicum_variation_41_94_250.variation.variation_id | 4 | Using where; Using index |
+----+-------------+-------------------------------+-------+---------------+---------------+---------+-----------------------------------------------------------------+----------+--------------------------+
2 rows in set (0.00 sec)
EXPLAIN SELECT COUNT(DISTINCT variation_id) FROM tmp_sample_genotype_single_bp INNER JOIN variation USING (variation_id);
+----+-------------+-------------------------------+-------+---------------+---------------+---------+-----------------------------------------------------------------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------+-------+---------------+---------------+---------+-----------------------------------------------------------------+----------+--------------------------+
| 1 | SIMPLE | variation | index | PRIMARY | PRIMARY | 4 | NULL | 71156260 | Using index |
| 1 | SIMPLE | tmp_sample_genotype_single_bp | ref | variation_idx | variation_idx | 4 | solanum_lycopersicum_variation_41_94_250.variation.variation_id | 4 | Using where; Using index |
+----+-------------+-------------------------------+-------+---------------+---------------+---------+-----------------------------------------------------------------+----------+--------------------------+
2 rows in set (0.00 sec)
EXPLAIN SELECT COUNT(*), COUNT(DISTINCT variation_id) FROM tmp_sample_genotype_single_bp INNER JOIN variation USING (variation_id);
+----+-------------+-------------------------------+-------+---------------+---------------+---------+-----------------------------------------------------------------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------+-------+---------------+---------------+---------+-----------------------------------------------------------------+----------+--------------------------+
| 1 | SIMPLE | variation | index | PRIMARY | PRIMARY | 4 | NULL | 71156260 | Using index |
| 1 | SIMPLE | tmp_sample_genotype_single_bp | ref | variation_idx | variation_idx | 4 | solanum_lycopersicum_variation_41_94_250.variation.variation_id | 4 | Using where; Using index |
+----+-------------+-------------------------------+-------+---------------+---------------+---------+-----------------------------------------------------------------+----------+--------------------------+
2 rows in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment