1

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.

  • 1
    did you try multicolumn index on (EventType, event_log_domain_id )? – Nikita Feb 12 at 0:59
  • MySQL can only (usually) use one index per table (or table alias) at a time. Compound indexes are usually the solution – danblack Feb 12 at 2:53
  • 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. – danblack Feb 12 at 4:35
  • "This is the db table structure (relevant parts)" -- The necessary part is all of SHOW CREATE TABLE. – Rick James Feb 16 at 19:02
  • @danblack, your statement also holds for DBMS that can use several indexes. – Lennart Feb 19 at 12:58
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).

|improve this answer|||||

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.