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.

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).