Index over multiple fields

Question

I have a table with ~40 million entries and I have an index for most of my fields. All my queries are optimized, except for one, which gets called a couple times per second and uses most of the fields. Now I'm investigating on how to optimize this query, do I:

• create an index over all fields?
• create an index over all fields and remove the individual indexes?

If I have an index over all fields, would I even need the individual indexes?

This is the query:

SELECT count(*) as rang_pos 
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR) 
AND results.speedtest_id = 1 
AND results.checked = 1 
AND results.typing_mode = 1 
AND results.keystrokes <= 500

Indexes

Answer 1

With a multi-field index, you need to make sure each field in the index is used from left to right in the WHERE clause, otherwise the index will not be applicable.
If we assume that your index speedtest_id_2 is the only index you have (except for the primary key), then the following query will use the index

SELECT count(*) as rang_pos 
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR) 
AND results.speedtest_id = 1 
AND results.checked = 1 
AND results.typing_mode = 1 
AND results.keystrokes <= 500

Whereas the following query will not use that index.

SELECT count(*) as rang_pos 
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR) 
AND results.checked = 1 
AND results.typing_mode = 1 
AND results.keystrokes <= 500

as the speedtest_id field which is first in the index is not part of the WHERE clause.

With your combined index, I would add keystrokes to the index before the created field, as that will help reduce the data being read through when calculating the output. Also use the EXPLAIN command to view the query plan that MySQL is going to use when running the query.

EXPLAIN SELECT count(*) as rang_pos 
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR) 
AND results.speedtest_id = 1 
AND results.checked = 1 
AND results.typing_mode = 1 
AND results.keystrokes <= 500

You may find that rearranging the fields in the multi-field index will change the query plan, and thereby the performance of the query.

Your speedtest_id single field index is not relevant, as the combined index has that field as the first field, so will be used even if you only filter on that one field.

Whether you leave the other individual field indexes in or remove them is entirely down to how your application works, and what other queries are run against it - you may find that they are all used at some point or another when your application runs.

You can leave your application running for a while, and then examine the following tables (if you have the sys schema installed) to see how the indexes are being used.

select * from sys.schema_index_statistics where object_schema = '{database}';
select * from sys.schema_unused_indexes where object_schema = '{database}';

This will give you an idea of which indexes are not used by the application.