0

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

##enter image description here

  • Can you add the output of show index from results; to your question please? Remember with a multi-column index, the fields need to be present in your query from left to right for the index to be used. – Dave Rix Nov 23 '16 at 12:48
  • @DaveRix I added the indexes. So if I create a multi-column index, I have to make sure that its the same order as the query calls the individual columns? – Christian Strang Nov 23 '16 at 12:56
  • No, sorry, I didn't explain myself very well, there is limited characters availalble in comments! I will explain more in my answer below :) – Dave Rix Nov 23 '16 at 13:04
  • 1
    SHOW CREATE TABLE tablename; is more helpful than show index. And please use text output, not images. – ypercubeᵀᴹ Nov 23 '16 at 13:23
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.

  • The sys schema is available in MySQL from version 5.6 onwards - I use it regularly to investigate index usage - dev.mysql.com/doc/refman/5.7/en/sys-schema-prerequisites.html – Dave Rix Nov 23 '16 at 13:25
  • Thank you for your answer! Just to clarify: If I want a combined index to be used, I have to make sure that every field inside the index is also used in the query? I'm just a bit confused about the following part: the index is used from left to right in the WHERE clause – Christian Strang Nov 23 '16 at 13:27
  • one more thing about adding keystrokes: wouldn't it make more sense to have "created" before "keystrokes"? Otherwise the query would first observe all rows, instead of those in the last 24 hours or doesn't it work like this? – Christian Strang Nov 23 '16 at 13:31
  • 1
    The fields used in your WHERE clause only need to match then first n fields in the index, and they don't have to be in the same order. You can use index fields 1,2,3 of a 4 field index, but not index fields 2,3,4. If you use index fields 1,3,4 then the index will be used, but only the first field, as field 2 is not in the WHERE clause. – Dave Rix Nov 23 '16 at 13:37
  • You could try switching the created and keystrokes field around in your index, both will work, but one may be more efficient than the other - always try to use the fields in order or cardinality, or how much data they will remove from the results if they are applicable. checked for example may not be a good index field, as there are only two values - unless there are only a handful of 'not checked' records, and you want to select for those - in which case it would be excellent! – Dave Rix Nov 23 '16 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.