0

I've seen some similar types of questions, however, I have not been able to find a best solution. i need this query optimized.

itemid           state_id                timestamp
======================================================
  1              1              2012-08-11 00:00:00
  1              2              2012-08-12 00:00:00
  1              3              2012-08-13 00:00:00
  2              1              2012-08-10 00:00:00
  2              2              2012-08-13 00:00:00   <=== lastest  state_id =2
  3              1              2012-08-10 00:00:00
  3              2              2012-08-13 00:00:00
  3              4              2012-08-15 00:00:00
  4              1              2012-08-10 00:00:00
  4              3              2012-08-13 00:00:00
  4              2              2012-08-16 00:00:00   <=== lastest state_id =2
  5              1              2012-08-16 00:00:00

I need to select itemid based on the latest timestamp and state_id = 2.

I have this query http://sqlfiddle.com/#!2/a3d41/1/0 but i think it is not optimized for large table, Any ideas? Thanks!

SELECT *
FROM hops h
WHERE h.timestamp = (
SELECT MAX( h2.`timestamp` )
FROM hops h2
WHERE h.`itemid` = h2.`itemid` )
AND h.state_id = 2
sentlamp
  • 13
  • 1
  • 1
  • 3
  • possible duplicate of [Mysql join based on max(timestamp)](http://stackoverflow.com/questions/1373559/mysql-join-based-on-maxtimestamp) – Hituptony May 09 '14 at 21:04

3 Answers3

1
SELECT * FROM hops a
inner join (SELECT itemid, MAX(timestamp) FROM hops group by itemid) b
ON a.itemid  = b.itemid
 where a.STATE_ID = 2

This is going to do an inner join on a sub-query which in effect should only select the records associated to the MAX timestamp value in the associated table.

You will be selecting fewer rows this way, which with the inner join will return only the rows you selected in the subquery but with the result output specified by doing the SELECT *

Now you got a join instead of using the WHERE clause, which is going to minimize how many times your SQL engine does lookups on your table. or table scans whatever you call it.

Hituptony
  • 2,740
  • 3
  • 22
  • 44
  • It would be nice if you add some explanation about why the @sentlamp's code isn't optimized and yours is. – Raydel Miranda May 09 '14 at 20:17
  • @Hituptony your query doesn't work correctly, shes returning rows with latest `state_id` != 2 – Thiago França May 09 '14 at 20:52
  • @ThiagoFrança um no YOU are returning rows with state_id = 2. look at OP!!! and put back my upvote! – Hituptony May 09 '14 at 20:57
  • @Hituptony look at sentlamp comment on my answer, he needs rows having where the last state_id = 2 even having state_id > 2 with oldest timestamp, correcting your answer i will put back your upvote. – Thiago França May 09 '14 at 21:00
  • @ThiagoFrança he doesn't say he needs state_id > 2 anywhere dude. you're trippin! he says that in your result set itemid 3 shouldn't be returned because the stateid is not 2! – Hituptony May 09 '14 at 21:02
  • and right here in his sqlfiddle he has an insert into record of (3, 2, '2014-05-02 00:00:00'), – Hituptony May 09 '14 at 21:03
  • @Hituptony look at the question, the rows he needs have a comment after in the example table. – Thiago França May 09 '14 at 21:04
  • then he needs to be clearer in his questioning. because it's very obvious to me that 3 has stateid of 2 and fits the criteria. If he only needs Itemid 2 and 4 then he can specify this in his WHERE clause. He said he wants it so be scalable. Mine is scalable. What you're saying is NOT – Hituptony May 09 '14 at 21:05
  • @Hituptony ok, but to the question your answer is not correct. correcting your answer i will put back your upvote! – Thiago França May 09 '14 at 21:06
  • thanks for all answers. i need to benchmark to find out. FYI but also i found same topic and totally different query [here][1] [1]: http://stackoverflow.com/questions/17038193/select-row-with-most-recent-date-per-user – sentlamp May 09 '14 at 21:09
  • my question is i need to select all items where their recent state is 2 – sentlamp May 09 '14 at 21:10
0

assuming your only looking for one record to be returned, you could use something like:

SELECT *
FROM hops h
WHERE
h.state_id = 2
ORDER BY timestamp DESC limit 1
Adam MacDonald
  • 1,958
  • 15
  • 19
0

I think you need to make a benchmark to see what is the best optimized solution.

SELECT itemid, timestamp 
FROM hops t1 
WHERE state_id = 2 
AND timestamp > (
   SELECT MAX(timestamp)
   FROM hops t2 
   WHERE t1.itemid = t2.itemid 
   AND t2.state_id != 2
)
GROUP BY itemid

I tested the code and its working fine you can see it running on sqlfiddle

Thiago França
  • 1,817
  • 1
  • 15
  • 20