Skip to content

Instantly share code, notes, and snippets.

@dbolser

dbolser/gist:4179754

Created December 1, 2012 00:29
Show Gist options
  • Save dbolser/4179754 to your computer and use it in GitHub Desktop.
Save dbolser/4179754 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
> EXPLAIN SELECT COUNT(DISTINCT feature_stable_id), COUNT(DISTINCT feature_stable_id, somatic) FROM transcript_variation ;
+----+-------------+----------------------+-------+---------------+---------------------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+-------+---------------+---------------------+---------+------+----------+-------------+
| 1 | SIMPLE | transcript_variation | index | NULL | somatic_feature_idx | 132 | NULL | 64279473 | Using index |
+----+-------------+----------------------+-------+---------------+---------------------+---------+------+----------+-------------+
> EXPLAIN SELECT COUNT(DISTINCT feature_stable_id), COUNT(DISTINCT feature_stable_id, somatic) FROM transcript_variation IGNORE INDEX (somatic_feature_idx);
+----+-------------+----------------------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------+---------------+------+---------+------+----------+-------+
| 1 | SIMPLE | transcript_variation | ALL | NULL | NULL | NULL | NULL | 64279473 | |
+----+-------------+----------------------+------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)
> SELECT COUNT(DISTINCT feature_stable_id), COUNT(DISTINCT feature_stable_id, somatic) FROM transcript_variation USE INDEX (somatic_feature_idx);
+-----------------------------------+--------------------------------------------+
| COUNT(DISTINCT feature_stable_id) | COUNT(DISTINCT feature_stable_id, somatic) |
+-----------------------------------+--------------------------------------------+
| 134558 | 134558 |
+-----------------------------------+--------------------------------------------+
1 row in set (3 min 1.68 sec)
> SELECT COUNT(DISTINCT feature_stable_id), COUNT(DISTINCT feature_stable_id, somatic) FROM transcript_variation IGNORE INDEX (somatic_feature_idx);
+-----------------------------------+--------------------------------------------+
| COUNT(DISTINCT feature_stable_id) | COUNT(DISTINCT feature_stable_id, somatic) |
+-----------------------------------+--------------------------------------------+
| 134558 | 134558 |
+-----------------------------------+--------------------------------------------+
1 row in set (2 min 47.31 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment