title: This slow MySQL Query needs improvement
body: <p>This query works and provides me with the information I need, but it is very slow: it takes 18 seconds to agregate a database of only 4,000 records.
  I'm bringing it here to see if anyone has any advice on how to improve it.</p>
  
  <pre><code>SELECT COUNT( status ) AS quantity, status 
  FROM log_table
  WHERE time_stamp
  IN (SELECT MAX( time_stamp ) FROM log_table GROUP BY userid )
  GROUP BY status
  </code></pre>
  
  <p>Here's what it does/what it needs to do in plain text:</p>
  
  <p>I have a table full of logs, each log contains a "userid", "status" (integer between 1-12) and "time_stamp" (a time stamp of when the log was created). There may be many entries for a particular userid, but with a different time stamp and status. I'm trying to get the most recent status (based on time_stamp) for each userid, then count the occurrences of each most-recent status among all the users.</p>
  
  <p>My initial idea was to use a sub query with GROUP BY userid, that worked fast - but that always returned the <em>first</em> entry for each userid, not the <em>most recent</em>. If I could do GROUP BY userid using time_stamp DESC to Identify which row should be the representative for the group, that would be great. But of course ORDER BY inside of group does not work. </p>
  
  <p>Any suggestions?</p>