Sabonzy
5 months ago

mysql index key not provided when not limiting query result

Posted 5 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.