SQL query speed

Take a look at these 2 queries

Query 1:

SELECT if(TIMESTAMPDIFF(SECOND,dtime,NOW()) > 90,0,counter) AS counter,
UNIX_TIMESTAMP(dtime) AS dtime
FROM ifInOctets_1
WHERE id = 100
ORDER BY dtime DESC
LIMIT 1

Query 2:

SELECT if(TIMESTAMPDIFF(SECOND,dtime,NOW()) > 90,0,counter) AS counter,
UNIX_TIMESTAMP(dtime) AS utime
FROM ifInOctets_1
WHERE id = 100
ORDER BY dtime DESC
LIMIT 1

Both produce the exact same result, 1 row with a unix timestamp and a counter. There is only 1 letter difference between the 2, but query 2 is orders of magnitude faster. With just under 100k rows in the table query 1 took 0.3240 sec and query 2 took 0.0012 sec thats 270 times faster and as the table grows the difference will increase. 

Lets have explain tell us why

Query 1: Using where; Using filesort used index to get id but needed to sort 100K rows to get the most recent row

Query 2: Using where used index to get the most recent row and only had to look at 183 rows to find the id

So why did it not use the index on query 1? Thats simple dtime is a datetime field but it was modified to be a unix_timestamp in the query and that does not match the index type, that caused the query optimiser to use the id index.

You might think that 0.3 seconds is still quite fast, but this query is used to build an image every minute and uses 246 queries, thats 74 seconds just to get the data and it still has to produce the image. With query 2 all 246 queries take less than 0.3 seconds.

Spend your time optimising your SQL it makes much more difference than you think.

This entry was posted in SQL. Bookmark the permalink.