Optimizing index performance for mysql

Question

The mysql version that I use is :

'aurora_version','1.17.98'
'innodb_version','1.2.10'
'protocol_version','10'
'slave_type_conversions',''
'version','5.6.10-log'
'version_comment','MySQL Community Server (GPL)'
'version_compile_machine','x86_64'
'version_compile_os','Linux'

This is the db table structure (relevant parts) that I m working with (table name access_logs) :

id| EventTime | EventType | EventName ... | event_log_domain_id

the table has approximately 11mil records.

These are the common ways that the data is queried :

1) Using in statement with event_log_domain_id and performing count

SELECT COUNT(*) FROM `access_logs ` WHERE `access_logs`.`event_log_domain_id ` IN (8, 59, 920, 1054, 2227) AND `access_logs `.`EventType ` = 1

2) Not Using in statement with event_log_domain_id and performing count

SELECT COUNT(*)
 FROM access_logs USE INDEX(event_log_domain_id)
 WHERE `access_logs`.`event_log_domain_id ` = 1304
 AND `access_logs`.`EventType ` = 1 

3) Not performing the count

SELECT `access_logs `.* FROM access_logs WHERE `access_logs `.`event_log_domain_id ` = 1304 AND `access_logs `.`EventType ` IN (1, 5) ORDER BY id desc LIMIT 50

I have indexes on both event_log_domain_id and EventType.

Count performance is by far the worst. It seems like count queries don't even use the indexing. Even when I provide the index name, for ex :

SELECT COUNT(*)
 FROM `access_logs` USE INDEX(event_log_domain_id)
 WHERE `access_logs`.`event_log_domain_id` IN (8, 59, 920, 1054, 2227 )
 AND `access_logs`.`EventType` = 1 

Does not help improve the performance much, may even make it worse.

On the other hand the select query(#3) does perform better when providing USE IDEX.

I was also thinking about adding composite index on both event_log_domain_id and EventType. Rebuilding indexes takes somewhat long so I don't want to just randomly try stuff out without understanding the performance implications first.

Is there a special index I can create for the count operations? At this point for the worst case scenario the query #1 and #2 runtime is about 240 sec or 120 each.

• NikitaSerbskiy: did you try multicolumn index on (EventType, event_log_domain_id )?
• danblack: MySQL can only (usually) use one index per table (or table alias) at a time. Compound indexes are usually the solution
• danblack: is the exact number required? You can pre-cache values base on the assumption that old logs aren't changed. If an estimate maybe you could look at the EXPLAIN {query1} output for estimated rows.
• Rick James: "This is the db table structure (relevant parts)" -- The necessary part is all of SHOW CREATE TABLE.
• Lennart - Slava Ukraini: @danblack, your statement also holds for DBMS that can use several indexes.

Answer 1

Beware! You have an extra space in some table and column names. (I removed them when wrapping the queries.)

This is the optimal index for all those queries, and there is no benefit in saying USE INDEX:

INDEX(EventType, event_log_domain_id) -- in this order
INDEX(event_log_domain_id, EventType) -- in this order for #3

This index will handle the WHERE and be "covering". Include both indexes.

More on indexing: http://mysql.rjweb.org/doc.php/index_cookbook_mysql It explains how the composite index should start with all columns tested with =, then move on to IN, and finally one "range". (Your examples have no ranges).