title: MySQL Subquery With LIMIT vs JOIN
body: <p>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.</p>
  
  <p>The <code>RunTime</code> column is a UNIX timestamp captured every night at midnight. Not every entry will occur every night, meaning an entry for <em>Entry1</em> may have occurred two days ago, but not today.</p>
  
  <p><em>Schema:</em></p>
  
  <pre><code> `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`),
  </code></pre>
  
  <p><em>Example Desired Results:</em></p>
  
  <pre><code>+-------------+----------------+----------+
  | Name        | LastRunTime        | Count    |
  +-------------+----------------+----------+
  | Random Name |     1339131600 |       73 |
  | RandomName2 |     1337131600 |       13 |
  ... etc
  </code></pre>
  
  <p>Essentially, my working query looks like the below. It will query the table for yesterdays data. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>The problem is, this query is slow. I want to move away from the first subquery, on the <code>RunTime</code> column, but the LIMIT and association is getting the best of me. The above takes a very, <em>very</em>, long time.</p>
  
  <p>Does anyone have an example of a way to:</p>
  
  <p>Get the second most recent <code>RunTime</code>, the count of rows for the second most recent <code>RunTime</code>, quickly and efficiently where <code>RunTime</code> is not consistent across all rows?</p>
  
  <p>Any suggestions are appreciated!</p>