Level 53
You might get more help asking this question in a MySQL specific forum ;)
Mar 25, 2019
7
Level 9
mysql index key not provided when not limiting query result
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.
Level 73
Unless there is a huge difference in execution times I wouldn't worry about it since the MySQL parser is probably smarter than both you and me. It will choose the best plan it can. :)
Level 9
@TRAY2 - depending on the time_updated range the difference between the two is significant. the table have records in millions
Level 73
Then it should use an index.
However I would move the between condition last since it probably gives the biggest span just to test.
Level 22
Could you put EXPLAIN in front of the query and share the results? Also the table structure might be usefull. Also you mentioned that the table has millions of records, how many exactly and how much rows will this query return?
Level 9
@dirkzz currently the table have 10 columns with no relationship and about 30M records and 144 records added every minutes, i did what @tray2 suggested and an index key is selected. depending on the date range of the BETWEEN condition these take minutes to run, the query is used to generate excel workbook and each sheet representing the day. database queue is used for this. when the data is large the processing times long and the job fails. I want how i can reduces the processing time and have the jobs run successfully
Level 73
SQL optimization is always tricky but usually moving the heaviest lifter (in this case the between) last usually helps a bit.
What you can try is to move older records to another table, let's say everything older than three months and then build check if the dates are in which table and build the query dynamically.
1 like
Please or to participate in this conversation.