MySQL Subquery With LIMIT vs JOIN

Question

I would prefer to use a JOIN for this, but none of the solutions are either viable due to performance impacts or they don't return the right result set.

The RunTime column is a UNIX timestamp captured every night at midnight. Not every entry will occur every night, meaning an entry for Entry1 may have occurred two days ago, but not today.

Schema:

 `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(32) NOT NULL,
  `Category` int(11) NOT NULL,
  `RunTime` int(11) NOT NULL,
  UNIQUE KEY `ID` (`ID`),

Example Desired Results:

+-------------+----------------+----------+
| Name        | LastRunTime        | Count    |
+-------------+----------------+----------+
| Random Name |     1339131600 |       73 |
| RandomName2 |     1337131600 |       13 |
... etc

Essentially, my working query looks like the below. It will query the table for yesterdays data.

select Name,
       RunTime AS LastRunTime,
       count(*) AS Count
from TABLE
where RunTime = 
    (
    select DISTINCT( RunTime ) from TABLE WHERE r.Name=Name order by RunTime LIMIT 1,1
    ) 
and Category in 
    (
    select AnotherTable .ID from AnotherTable where AnotherTable.Status = 1
    )

group by Name

The problem is, this query is slow. I want to move away from the first subquery, on the RunTime column, but the LIMIT and association is getting the best of me. The above takes a very, very, long time.

Does anyone have an example of a way to:

Get the second most recent RunTime, the count of rows for the second most recent RunTime, quickly and efficiently where RunTime is not consistent across all rows?

Any suggestions are appreciated!

What do you indexes look like? What is result of running the query and the sub query with an `EXPLAIN` in front. — theon, Aug 06 '12 at 19:26

score 1 · Answer 1 · edited Aug 07 '12 at 14:29

1

    Select Name, Max(RunTime ) as  LastRunTime        ,Count(*) as Count
    from TABLE a
    Inner join AnotherTable b ON a.Category =b.ID and   b.Status = 1
    GROUP BY name
    Having LastRunTime < Max(RunTime )

edited Aug 07 '12 at 14:29

Mike Mackintosh

13,917
6
60
87

answered Aug 06 '12 at 19:26

Ruzbeh Irani

2,318
18
10

Why are you doing `MAX(RunTime)` twice? In your `Having` you could say `HAVING RunTime < LastRunTime` – arnoudhgz Aug 06 '12 at 21:00
@arnoudhgz, you need the `MAX()` as having should only be used with some sort of comparison function, otherwise you should just use a where. – Mike Mackintosh Aug 07 '12 at 14:27
now, with your last edit, the query can't get results. You're now saying that only the result of something smaller than itself can be in the result..... (`LastRuntime` is the same `MAX(RunTime)`, so `LastRunTim < MAX(RunTime)` will give you 0 results) – arnoudhgz Aug 07 '12 at 15:04
@sixeightzero `LastRunTime` is not a table column. `HAVING RunTime < LastRunTime` should do the trick for this query – arnoudhgz Aug 07 '12 at 15:05
@arnoudhgz, i get column not found when using RunTime. If i pass LastRunTime, the having works, but it pulls ALL records that are not the current runtime. – Mike Mackintosh Aug 07 '12 at 16:02

score 0 · Accepted Answer · answered Aug 08 '12 at 17:49

0

I stuck with a subquery but changed the timeframe to WHERE RunTime = (SELECT MAX(RunTime) - 86400....

All other solutions or attempts where either too demanding or convoluted.

answered Aug 08 '12 at 17:49

Mike Mackintosh

13,917
6
60
87

MySQL Subquery With LIMIT vs JOIN

2 Answers2