In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows:
+----------------+-------------+-----------------+--------+
| participant_id | question_id | given_answer_id | status |
+----------------+-------------+-----------------+--------+
| 500 | 12 | 25 | 0 |
+----------------+-------------+-----------------+--------+
The combination participant_id + question_id + given_answer_id is unique.
At the moment we have following keys:
PRIMARY KEY (in this order)
- participant_id
- question_id
- given_answer_id
INDEX KEY
- question_id
For this table, we have two kinds of select queries in our application:
[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;
and
[...] WHERE question_id = x;
Normally, every participant_id has between 0 and <100 rows with different question_ids. The other way around, every question_id can have unlimited (usually not more than 100 000) rows with different participant_ids. The first query is executed way more often than the second one.
When we execute following query, it shows us that 32096 rows where looked up:
EXPLAIN SELECT * FROM example WHERE question_id = 500;
+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+
| 1 | SIMPLE | example | ref | question_id | question_id | 8 | const | 32096 | |
+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+
Whereas, when we execute the same query without EXPLAIN only 18732 rows are returned.
What indexes do we need on this table in order to prevent this overhead but still performing for both kinds of queries?
Here's is the code to create this table:
CREATE TABLE `example` ( `participant_id` BIGINT(20) UNSIGNED NOT NULL, `question_id` BIGINT(20) UNSIGNED NOT NULL, `given_answer_id` BIGINT(20) UNSIGNED NOT NULL, `status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`participant_id`, `question_id`, `given_answer_id`), INDEX `question_id` (`question_id`) ) ENGINE=InnoDB;