Skip to content

Instantly share code, notes, and snippets.

@dbolser
Last active August 29, 2015 13:56
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/9131171 to your computer and use it in GitHub Desktop.
Save dbolser/9131171 to your computer and use it in GitHub Desktop.
DROP TABLE IF EXISTS test_et;
CREATE TABLE test_et (
exon_id int unsigned NOT NULL,
transcript_id int unsigned NOT NULL,
rank int NOT NULL,
##
PRIMARY KEY (exon_id, transcript_id, rank)#,
#KEY exon (exon_id),
#KEY transcript ( transcript_id)
);
INSERT INTO test_et VALUES
(289078, 151716, 2),
(289078, 151717, 2),
(289078, 151718, 2),
(289078, 151719, 2),
(289078, 151720, 2);
#OPTIMIZE TABLE test_et;
SELECT COUNT(*),
COUNT(DISTINCT exon_id) AS a,
COUNT(DISTINCT transcript_id) AS b,
COUNT(DISTINCT rank) AS c
FROM test_et;
## WRONG!
+----------+---+---+---+
| COUNT(*) | a | b | c |
+----------+---+---+---+
| 5 | 5 | 5 | 5 |
+----------+---+---+---+
## MySQL is misusing the index...
SELECT COUNT(*),
COUNT(DISTINCT exon_id) AS a,
COUNT(DISTINCT transcript_id) AS b,
COUNT(DISTINCT rank) AS c
FROM test_et
IGNORE INDEX (PRIMARY);
## RIGHT!
+----------+---+---+---+
| COUNT(*) | a | b | c |
+----------+---+---+---+
| 5 | 1 | 5 | 1 |
+----------+---+---+---+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment