6
SELECT DISTINCT d.customer_id, d.date_added FROM `order` d
WHERE d.customer_id NOT IN (
    SELECT DISTINCT i.customer_id 
    FROM `order` i 
    WHERE i.date_added > '2015-02-15 14:00:00'
)
ORDER BY d.date_added DESC;

The above query should return customer_id of customers who have not ordered after 15 Feb 2015 (I think). But very first record is
17168, 2015-08-16 17:36:00

What am I doing wrong?

This below query

SELECT DISTINCT i.customer_id,i.date_added FROM `order` i 
WHERE i.date_added > '2015-02-15 14:00:00' 
ORDER BY i.date_added ASC;

returns expected result i.e. list of customer ids for orders placed after 15 Feb

P.S. customer_id can not be NULL

P.P.S.

mysql> SHOW CREATE TABLE `order`;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| order | CREATE TABLE `order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_no` int(11) NOT NULL,
  `invoice_prefix` varchar(26) NOT NULL,
  `store_id` int(11) NOT NULL,
  `store_name` varchar(64) NOT NULL,
  `store_url` varchar(255) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `customer_group_id` int(11) NOT NULL,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `email` varchar(96) NOT NULL,
  `telephone` varchar(32) NOT NULL,
  `fax` varchar(32) NOT NULL,
  `custom_field` text NOT NULL,
  `payment_firstname` varchar(32) NOT NULL,
  `payment_lastname` varchar(32) NOT NULL,
  `payment_company` varchar(40) NOT NULL,
  `payment_address_1` varchar(128) NOT NULL,
  `payment_address_2` varchar(128) NOT NULL,
  `payment_city` varchar(128) NOT NULL,
  `payment_postcode` varchar(10) NOT NULL,
  `payment_country` varchar(128) NOT NULL,
  `payment_country_id` int(11) NOT NULL,
  `payment_zone` varchar(128) NOT NULL,
  `payment_zone_id` int(11) NOT NULL,
  `payment_address_format` text NOT NULL,
  `payment_custom_field` text NOT NULL,
  `payment_method` varchar(128) NOT NULL,
  `payment_code` varchar(128) NOT NULL,
  `shipping_firstname` varchar(32) NOT NULL,
  `shipping_lastname` varchar(32) NOT NULL,
  `shipping_company` varchar(40) NOT NULL,
  `shipping_address_1` varchar(128) NOT NULL,
  `shipping_address_2` varchar(128) NOT NULL,
  `shipping_city` varchar(128) NOT NULL,
  `shipping_postcode` varchar(10) NOT NULL,
  `shipping_country` varchar(128) NOT NULL,
  `shipping_country_id` int(11) NOT NULL,
  `shipping_zone` varchar(128) NOT NULL,
  `shipping_zone_id` int(11) NOT NULL,
  `shipping_address_format` text NOT NULL,
  `shipping_custom_field` text NOT NULL,
  `shipping_method` varchar(128) NOT NULL,
  `shipping_code` varchar(128) NOT NULL,
  `comment` text NOT NULL,
  `total` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `order_status_id` int(11) NOT NULL,
  `affiliate_id` int(11) NOT NULL,
  `commission` decimal(15,4) NOT NULL,
  `marketing_id` int(11) NOT NULL,
  `tracking` varchar(64) NOT NULL,
  `language_id` int(11) NOT NULL,
  `currency_id` int(11) NOT NULL,
  `currency_code` varchar(3) NOT NULL,
  `currency_value` decimal(15,8) NOT NULL DEFAULT '1.00000000',
  `ip` varchar(40) NOT NULL,
  `forwarded_ip` varchar(40) NOT NULL,
  `user_agent` varchar(255) NOT NULL,
  `accept_language` varchar(255) NOT NULL,
  `date_added` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `payment_company_id` varchar(32) NOT NULL,
  `payment_tax_id` varchar(32) NOT NULL,
  `review_requested` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`order_id`),
  KEY `store_id` (`store_id`),
  KEY `customer_id` (`customer_id`),
  KEY `customer_group_id` (`customer_group_id`),
  KEY `payment_country_id` (`payment_country_id`),
  KEY `payment_zone_id` (`payment_zone_id`),
  KEY `shipping_country_id` (`shipping_country_id`),
  KEY `shipping_zone_id` (`shipping_zone_id`),
  KEY `order_status_id` (`order_status_id`),
  KEY `affiliate_id` (`affiliate_id`),
  KEY `marketing_id` (`marketing_id`),
  KEY `language_id` (`language_id`),
  KEY `currency_id` (`currency_id`),
  KEY `payment_company_id` (`payment_company_id`),
  KEY `payment_tax_id` (`payment_tax_id`)
) ENGINE=MyISAM AUTO_INCREMENT=39783 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
  • 1
    @Mihai The objective is show customers who have NOT ordered after 15 Feb 2015 – OC2PS Aug 16 '15 at 19:52
  • @ypercube datetime – OC2PS Aug 16 '15 at 19:52
  • Exact version of mysql? – ypercubeᵀᴹ Aug 16 '15 at 19:53
  • 1
    Can you add the SHOW CREATE TABLE `order`; output as well? This looks like a bug. – ypercubeᵀᴹ Aug 16 '15 at 20:04
  • 1
    The query you posted looks fine. Are you sure that's exactly what you were executing? – Martin Smith Aug 16 '15 at 20:20
4

This looks like a bug. The improvements to the optimizer regarding anti-joins and subquery materialization, introduced in 5.6, seem relevant. There was a similar bug (#73368), related to subquery materialization but it has been fixed in 5.6.22 version.

I suggest you first, try running the query with each of the following options and check if you get the same unexpected/wrong results:

set optimizer_switch = 'semijoin=off';

set optimizer_switch = 'materialization=off';

Just noticed that it's a MyISAM table, so it's worth checking as well if it is a corruption issue (you can CHECK and/or REPAIR the table).

Then update your installation with the latest 5.6.26 version and check again with default options (and again with the 2 options above).

If you are still getting wrong results, report the bug to MySQL, adding the query, the table CREATE statement, the EXPLAIN output and uploading a dump of the table as well, so they can reproduce it. If you can reproduce the bug with a smaller table (just the 2-3 columns and a few rows) would be much better than a 50-column and a thousands rows table.

3

Switch to NOT EXISTS:

Don't use NOT IN ( SELECT ... ), it optimizes poorly, even with "materialization". Instead, use LEFT JOIN .. IS NULL or NOT EXISTS. That should also obviate the need for DISTINCT.

SELECT  d.customer_id, d.date_added
    FROM  `order` d
    WHERE  NOT EXISTS
      ( SELECT  *
            FROM  `order`
            WHERE  customer_id = d.customer_id
              AND  date_added > '2015-02-15 14:00:00' 
      )
    ORDER BY  d.date_added DESC; 

To make my subquery run much faster, change

KEY `customer_id` (`customer_id`)

to the "composite" key:

KEY `customer_id` (`customer_id`, date_added)

GROUP BY

Well, maybe you did need the DISTINCT, but it will not give you one line per user. GROUP BY can:

SELECT  d.customer_id,      -- no dups here
        MAX(d.date_added)   -- last order date
    FROM  `order` d
    WHERE  NOT EXISTS 
      ( SELECT  *
            FROM  `order`
            WHERE  customer_id = d.customer_id
              AND  date_added > '2015-02-15 14:00:00' 
      )
    GROUP BY  d.customer_id
    ORDER BY  MAX(d.date_added) DESC; 

HAVING

Even simpler (assuming I have not lost your semantics), is

SELECT  customer_id,
        MAX(date_added) AS last_order
    FROM  `order`
    GROUP BY  customer_id
    HAVING    last_order < '2015-02-15 14:00:00'
    ORDER BY  last_order DESC; 

Unrelated schema recommendations:

  • The payment_* and shipping_* fields are probably repeated both within a row and between rows. I assume, for example, you don't have the user retype all that stuff each time they order something? Suggest you 'normalize' that collection of fields into a new table.

  • Also, having company and company_id (ditto for zone) makes no sense. Put the lengthy name in another table.

  • For company_id, use the 2-letter standard instead of the 4-byte INT. (And then make it CHAR(2) CHARACTER SET ascii to avoid the unnecessary utf8.)

  • I see no composite indexes; I suspect you would benefit from some others. Look at your other SELECTs together with my index cookbook.

  • the payment_* and shipping_* makes sense because are the easiest and more simple method to have the correct data in the database. should you update the delivery address of the customer record after the order, the order will still have the proper data. there are other options but this one imho is effective with low effort. – Paolo Aug 17 '15 at 6:31

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.