CODEWITHSUNDEEP
mysqlsqlindexing
Javi Prieto
Javi Prieto

Reputation: 429

Slow select in mysql

I'm having problems with a query in mysql. For plotting a chart, I'm creating a temporary table with 'X SECOND' intervals with this simple structure:

CREATE TEMPORARY TABLE intervals (date DATETIME NOT NULL PRIMARY KEY)

and querying this SELECT: 

SELECT COUNT(dt.Device) AS count, i.date 
FROM intervals i LEFT JOIN device_tracker dt ON 
    i.date <= dt.date AND 
    i.date + INTERVAL X SECOND >= dt.date 
GROUP BY i.date

Device is primary key and dt.date is index.

with 20000 rows in the device_tracker AND 30-35 intervals query takes about 4-5 seconds. Is that normal? Can I do something to improve the speed?

This is the explain:

id  select_type table   type    possible_keys   key      key_len  ref   rows    Extra
-------------------------------------------------------------------------------------------------------------------
1    SIMPLE      i       index   NULL            PRIMARY  8        NULL  29      Using index; Using temporary; Using filesort
1    SIMPLE      dt      ALL     date            NULL     NULL     NULL  21594

EDIT: Last_time doesn't exists, I'm using date instead.

Upvotes: 1

Views: 176

Answers (2)

Javi Prieto
Javi Prieto

Reputation: 429

from Galz response:

try using HAVING instead of WHERE: SELECT ... LEFT JOIN ... GROUP BY ... HAVING i.date IS NOT NULL

It worked.

Upvotes: 1

dmcnelis
dmcnelis

Reputation: 2923

The issues is possibly the Last_Time field in device_tracker. You might try an index on both date and last_time...as in:

CREATE INDEX myGreatIdx ON device_tracker(date, Last_Time);

Upvotes: 0

Related Questions