Get a list of orders by excluding the recents one in the same category?

Question

I have a table of the following structure (simplified):

Table orders

id
created
name
executed
category_id

I need a list of n orders from distinct categories (if n is 5, I should have at most 5 different categories, and never two same categories). I successfully done this one using the following request :

SELECT   name 
FROM     orders 
WHERE    executed IS NULL 
GROUP BY category_id LIMIT 5;

But I'd need to exclude the categories that has already been processed less than an hour ago.

For that, I was able to do the following query :

SELECT   name, category_id 
FROM     orders 
WHERE    executed IS NULL 
AND      category_id NOT IN (SELECT   category_id 
                             FROM     orders 
                             WHERE    executed > (UTC_TIMESTAMP() - INTERVAL 60 MINUTE) 
                             GROUP BY category_id) 
GROUP BY category_id 
LIMIT 5;

But I was wondering if there was a faster way to get the results.

I've made an SQLFiddle to show my example, but I find it quite slow and I was not able to get a successful result from them.

Here's an alternative version with a LEFT JOIN I tried but it doesn't work:

SELECT  u1.some, u1.columns
    FROM  orders AS u1
    LEFT JOIN  orders AS u2  ON u1.category_id = u2.category_id
      AND  u2.executed > (UTC_TIMESTAMP() - INTERVAL 5 SECOND)
    WHERE  u1.created < (UTC_TIMESTAMP() - INTERVAL 60 SECOND)
      AND  (u1.executed IS NULL
              OR  u1.executed < (UTC_DATE() - INTERVAL 1 MONTH)
           )
      AND  u2.category_id IS NOT NULL
      AND  u1.category_id NOT IN u2.category_id
    LIMIT  10;

Answer 1

You can speed up the query by adding an index as follows:

ALTER TABLE `orders` ADD KEY ix_executed(executed,category_id);

Create table orders as follows:

CREATE TABLE `orders` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(250) NOT NULL,
  `executed` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `category_id` tinyint(3) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_executed` (`executed`,`category_id`)
) ENGINE=InnoDB; 

Rest of your query seems good to me.

Answer 2

SELECT  u1.some, u1.columns
    FROM       orders AS u1
    LEFT JOIN  orders AS u2  ON u1.category_id = u2.category_id
    WHERE  u1.created  < (UTC_TIMESTAMP() - INTERVAL 60 SECOND)
      AND  u2.executed > (UTC_TIMESTAMP() - INTERVAL 5 SECOND)
      AND  (u1.executed IS NULL
              OR  u1.executed < (UTC_DATE() - INTERVAL 1 MONTH)
           )
      AND  u2.category_id IS NULL
    LIMIT  10;

ON should include only the condition(s) that tie the two tables together.

LEFT JOIN ... IS NULL is how to say "it's in the left table, but not the right".

(I don't know if the rest of the WHERE is what you needed; I just copied your stuff.)

Unless you don't care about which 10, you need an ORDER BY.