CODEWITHSUNDEEP
mysqlsql
Vasudaprime
Vasudaprime

Reputation: 23

SQL - Slow subquery

Firstly I apologize for the "newbie"-like nature of this question. I saw many other slow-subquery questions on stack-exchange, but I wasn't sure how to apply the fixes to my problem. I'm rather new to any SQL beyond simple selects, inserts, etc.

I have a printer monitoring system which logs its results to a MySQL table (trends_uint) every five minutes for a range of devices. It logs the: device id (itemid), timestamp (clock), and pages printed (value_avg). From PhpMyAdmin I can see the table index is a combination of itemid and clock which I guess together provides a unique value. So far the table has around 2 million rows.

My query is as follows:

SELECT
     tu1.itemid AS trends_uint_itemid,
     tu1.clock AS time_value,
     tu1.value_avg AS pages
FROM
     trends_uint tu1
WHERE
    (tu1.clock = (
                     SELECT max(tu2.clock)
                     FROM trends_uint tu2
                     WHERE tu1.itemid = tu2.itemid
                  )
    )
ORDER BY tu1.clock DESC;

What I'm trying to do, is select the very latest value (i.e. the value_avg for the highest clock) for each device (itemid), so that I can hopefully chart the number of pages each printer has printed thus far.

I've tried running an EXPLAIN for the query which returned the following:

id  select_type         table   type    possible_keys   key         key_len     ref                 rows        Extra
1   PRIMARY             tu1     ALL     NULL            NULL        NULL        NULL                1527815     Using where; Using filesort
2   DEPENDENT SUBQUERY  tu2     ref     PRIMARY         PRIMARY     8           zabbix.tu1.itemid   115301      Using index

Any help would be appreciated. Thanks in advance.

Upvotes: 2

Views: 178

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Your query is fine. You need an index on trends_uint(itemid, clock).

You can also formulate the subquery as:

tu1.clock = (
                     SELECT tu2.clock
                     FROM trends_uint tu2
                     WHERE tu1.itemid = tu2.itemid
                     order by tu2.clock desc
                     limit 1
                  )

To ma, this form makes it clearer how the index gets used (go into the index for the item, choose the last clock value).

I would also suggest that you include an auto-incrementing id in the table as a primary key. This may help you speed up queries where you are trying to get the most recent rows in the table.

Upvotes: 0

Neil Mussett
Neil Mussett

Reputation: 708

Does it make a difference if you use an explicit subquery?

SELECT
     tu1.itemid AS trends_uint_itemid,
     tu1.clock AS time_value,
     tu1.value_avg AS pages
FROM
     trends_uint tu1
JOIN

(
     SELECT 
         itemid as theItem
         ,max(tu2.clock) AS LatestTime
     FROM trends_uint tu2
     GROUP BY itemid
) LatestClockForEachItem

ON tu1.itemid = LatestClockForEachItem.theItem
AND tu1.clock = LatestClockForEachItem.LatestTime

ORDER BY tu1.clock DESC;

PS. SQL Fiddle Here: http://sqlfiddle.com/#!2/bac3b/2

Upvotes: 0

zerkms
zerkms

Reputation: 254916

How about such query:

SELECT ...
FROM trends_uint t
INNER JOIN (
    SELECT MAX(clock) AS clock, itemid
    FROM trends_uint
    GROUP BY itemid
) x ON x.itemid = t.itemid AND t.clock = x.clock

Assuming you have a compound index in your table: itemid + clock (in this particular order)

Upvotes: 1

D-Rock
D-Rock

Reputation: 2676

The problem is the fact that the sub query is of type "DEPENDENT SUBQUERY". This means that MySQL is running that sub query once for every matching row of the primary query. If you say the table has 2million rows that means approximately 2 million times.

Try moving the logic of the subquery up to the primary query using the Group By operator or using a join.

Upvotes: 0

Related Questions