Consider the query:
Mysql> SET @run = 15;
Mysql> EXPLAIN SELECT objective, stage, COUNT (stage)
FROM iteminformation
WHERE run=@run AND objective=’7.1’
GROUP BY objective,stage
ORDER BY stage;
The iteminformation table has the following indexes;
Mysql> SHOW INDEXES FROM iteminformation: 
This query is run several times in an application with different values in the WHERE clause in a
growing data set.
What is the primary improvement that can be made for this scenario?
A.
Execute the run_2 index because it has caused a conflict in the choice of key for this query.
B.
Drop the run_2 index because it has caused a conflict in the choice of key for this query.
C.
Do not pass a user variable in the WHERE clause because it limits the ability of the optimizer to
use indexes.
D.
Add an index on the objective column so that is can be used in both the WHERE and GROUP
BY operations.
E.
Add a composite index on (run,objective,stage) to allow the query to fully utilize an index.
Explanation:
E.
Answer A doesn’t make grammatical sense at all. Maybe they meant FORCE or USE instead of EXECUTE.
Run_2 index isn’t conflicting, because it’s way better than the Run index (which has the name field, which isn’t used at all). Like Steve said, the RIGHT answer would be E — the composite index (which would also have the benefit of being able to read the entire SELECT from the INDEX itself.
B
Jeanrock why B?
E
D is the right answer less cost, highest improvement.
E would be too expensive and it couldn’t be used for group by.
E. Tested:
Creating an index on the objective column (Option D), the explain plan is the same:
create index o on i3(objective);
EXPLAIN SELECT objective, stage, COUNT(stage) FROM i3 WHERE run=@run AND objective=’7.1′ GROUP BY objective,stage;
+—-+————-+——-+——+—————+——+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——-+——+————-+
| 1 | SIMPLE | i3 | ref | run,run2,o | run2 | 5 | const | 320 | Using where |
+—-+————-+——-+——+—————+——+———+——-+——+————-+
Creating the composite index, MySQL uses it:
create index comp on i3(run, objetive, stage);
EXPLAIN SELECT objective, stage, COUNT(stage) FROM i3 WHERE run=@run AND objective=’7.1′ GROUP BY objective,stage;
+—-+————-+——-+——+—————+——+———+————-+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+————-+——+————————–+
| 1 | SIMPLE | i3 | ref | run,run2,comp | comp | 28 | const,const | 64 | Using where; Using index |
+—-+————-+——-+——+—————+——+———+————-+——+————————–+
I think E would be wright answer
E