Ordering, grouping and filtering SQL result sets

I've got a number of 'containers' in a database, each of which contains zero or more items. Each item has a name, score, timestamp representing it was added to the container, and a foreign key on the container ID.

I want to fetch all the containers where the top item has a score of 5 or greater (which implies not returning empty containers). As containers act like stacks in this instance, the item with the highest 'added time' is considered the 'top' item.

At present, I'm using the following SQL:

SELECT * FROM (
  SELECT name, container_id, score
  FROM items
  ORDER BY added_time DESC
) AS temptbl
GROUP BY container_id
HAVING score >= 5

This appears to give me the desired results, but it is incredibly slow when the number of items starts to increase - running the qu开发者_运维知识库ery on 8000 containers and 10000 items takes nearly 6 seconds on the MySQL console, which is too slow. Am I doing something obviously inefficient?

Maybe this is what you want:

SELECT name, container_id, score
FROM items AS tb1
RIGHT JOIN (SELECT container_id, Max(added_time) as added_time 
    FROM items GROUP BY tablename) as tb2 on 
    tb1.container_id = tb2.container_id AND tb1.added_time = tb2.added_time
WHERE score >= 5

Try any of the following. It relies on (container_id, added_id) being unique.

select *
  from (select container_id, max(added_time) as added_time
          from items
         group by container_id
       ) as topitems 
  join items on(topitems.container_id = items.container_id and 
                topitems.added_time   = items.added_time)
 where items.score >= 5;


select *
  from items a
 where score >= 5
   and (added_time) = (select max(b.added_time)
                         from items b
                        where a.container_id = b.container_id);

Turns out that the inner select had a LEFT JOIN which was causing the slowdown - removing that reduced the query time to 0.01s. It means losing the information brought in by the join, but that can be filled in afterwards (final number of rows returned is 'small' so it doesn't matter if I have to run a query for each to replicate the effects of a LEFT JOIN).