0

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;
2
  • NOT IN ( SELECT ... ) can perform very poorly; turn it into a LEFT JOIN. Or NOT EXISTS ( SELECT * ... )
    – Rick James
    Mar 16 '17 at 4:53
  • I've tested with more entries, and indeed, it's beginning to get a bit slow (my request). I've tried yours but can not make it work (mostly using the left join). I've updated my question to include a left join. I suspect it's around the u1.category_id NOT IN u2.category_id but I don't know what to change. Could you help me @RickJames ? Thank you :)
    – Cyril N.
    Mar 17 '17 at 11:29
0

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.

1
  • I'm sorry I didn't mentionned it on sqlfiddle nor on the question but there is already a few indexes on that table : executed, category_id, created and id. But I didn't add an index on executed+category_id at the same time. Does it changes much?
    – Cyril N.
    Mar 19 '17 at 15:21
0
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.

2
  • What will differ from settings the clause in the on or the where? I've made some tests with settings the where on the on and the results where apparently coherent?
    – Cyril N.
    Mar 19 '17 at 15:22
  • Clarity. That is, as a reader, I like the ON to reflect how the tables are related, and the WHERE to do the filtering. In most, but not all cases, the effect is identical, whether a condition is in the ON or in the WHERE. LEFT JOIN is where it matters. Do EXPLAIN EXTENDED SELECT ...; SHOW WARNINGS; to see how the statement is executed.
    – Rick James
    Mar 19 '17 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.