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

MySQL

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;

Best Answer

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.

Related Question