Sabonzy
195
7
General

mysql index key not provided when not limiting query result

Posted 2 months ago by Sabonzy

I have two index group on my table called smp, when I run explain on the same query without limiting the query results, no key is selected, but a key is selected when limiting is added.

I have these two indexes:

  • smp_time_updated_group_name_sub_group_index (time_updated, group_name, sub_group)

  • smp_time_updated_group_name_sub_group_name_index (time_updated, group_name, sub_group, name)

When i run this query:

EXPLAIN SELECT
     `name`,
     `group_name`,
     `sub_group`,
     `value`,
     `units`,
     `time_updated` 
 FROM
     `smp` 
 WHERE
     `group_name` = 'System Data' 
     AND `sub_group` = 'System' 
     AND `time_updated` BETWEEN '2019-01-01 00:00:00' 
     AND '2019-02-28 08:43:00' 
     AND `name` IN ( 'System Demand', 'System Generation' ) 
 ORDER BY
     `time_updated` DESC 
     LIMIT 26
    OFFSET 0

MySQL will select one of the possible index key.

But this query:

EXPLAIN SELECT
       `name`,
       `group_name`,
       `sub_group`,
       `value`,
       `units`,
       `time_updated` 
   FROM
       `smp` 
   WHERE
       `group_name` = 'System Data' 
       AND `sub_group` = 'System' 
       AND `time_updated` BETWEEN '2019-01-01 00:00:00' 
       AND '2019-02-28 08:43:00' 
       AND `name` IN ( 'System Demand', 'System Generation' ) 
   ORDER BY
       `time_updated` DESC

selects none.

I expect the index key to be selected even when the results is not been limited.

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.