CODEWITHSUNDEEP
mysql
Fraser73
Fraser73

Reputation: 41

MySQL Slow query after approx 12000 rows returned

I've got a table which is: power_ref INT primary key watts INT time DATETIME

It's got a few million rows, it's basically the current power consumption of my house every six seconds.

To generate a graph of a day's entries, I have the following query (slightly modified as I really replace the manually entered date with now()):

"select watts as Watts, time as Time from power where date(time)=date('2015-07-10') order by time desc limit 14400;"

There is an index on the "time" column, but this query ignores it. It takes about 15seconds to run.

However, if I issue the following:

"select watts as watts, time as time from power where date(time)=date('2015-07-10') order by time desc limit 12000;"

The query completes in about 0.1sec, and MySQL uses the index on the time column.

I've tried the following (to force use of the index, but MySQL still won't use the index)

"select watts, time from power USE INDEX (timeIndex) where date(time)=date('2015-07-10') order by time desc limit 14400;"

I find if the index is used by issuing "explain" in front of the query.

I've put the limit of 14400 as this is the max amount of entries in a day, not really needed, but just in case.

Upvotes: 0

Views: 210

Answers (1)

Norbert
Norbert

Reputation: 6084

The optimizer determined that a full table scan would be quicker. This behaviour changes once the data volume changes. USE INDEX is not forcing the index, it is forcing mysql to disregard other indexes. FORCE INDEX will force the use of the index:

select watts, time from power FORCE INDEX (timeIndex) 
where date(time)=date('2015-07-10') order by time desc limit 14400;

Should get you your wanted index behaviour

Upvotes: 0

Related Questions