2

I have this query, it works fine and takes over 2.5021 secs:

SELECT c.id AS 'id', count(c.id) as orders, o.created_at as last_order 
FROM customer c, order_2016 o 
WHERE o.customer_relacional_id = c.id 
GROUP BY c.id 
ORDER BY o.created_at DESC

But I want to request the customers with 0 orders too, then I try:

SELECT c.id AS 'id', count(o.customer_relacional_id) orders, o.created_at
FROM customer c
LEFT JOIN order_2016 o
ON (o.customer_relacional_id = c.id)
GROUP BY o.customer_relacional_id  
ORDER BY o.created_at  DESC

But this query never ends :S

EXPLAIN:

EXPLAIN 
SELECT c.id AS 'id', count(c.id) orders, o.created_at 
FROM customer c 
LEFT JOIN order_2016 o ON (o.customer_relacional_id = c.id) 
GROUP BY c.id 
ORDER BY o.created_at DESC

id select_type table type  possible_keys key    key_len ref  rows    Extra
1  SIMPLE      c     index NULL          email  212     NULL 987470  Using index; Using temporary; Using filesort
1  SIMPLE      o     ALL   NULL          NULL   NULL    NULL 240979

I'm trying to create an index in order_2016.customer_relacional_id but is taking too long

CREATE INDEX index_customer ON order_2016 (customer_relacional_id) USING BTREE; 

meanwhile in order_2015 it was fast

CREATE INDEX index_customer ON order_2015 (customer_relacional_id) USING BTREE;
Query OK, 0 rows affected (8.60 sec)
Records: 0  Duplicates: 0  Warnings: 0

Maybe there is something corrupt in order_2016 uhm

Finally I reset mysql and with the index it works slow, but works:

995625 rows in set (15.63 sec)

+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000093 |
| Waiting for query cache lock   |  0.000007 |
| checking query cache for query |  0.000071 |
| checking permissions           |  0.000008 |
| checking permissions           |  0.000007 |
| Opening tables                 |  0.000053 |
| System lock                    |  0.000014 |
| Waiting for query cache lock   |  0.000032 |
| init                           |  0.000032 |
| optimizing                     |  0.000008 |
| statistics                     |  0.000029 |
| preparing                      |  0.000020 |
| Creating tmp table             |  0.000032 |
| Sorting for group              |  0.000020 |
| executing                      |  0.000005 |
| Copying to tmp table           | 11.926916 |
| converting HEAP to MyISAM      |  0.049412 |
| Copying to tmp table on disk   |  2.910412 |
| Sorting result                 |  0.421047 |
| Sending data                   |  0.315151 |
| end                            |  0.000035 |
| removing tmp table             |  0.007676 |
| end                            |  0.000036 |
| query end                      |  0.000018 |
| closing tables                 |  0.000025 |
| freeing items                  |  0.000047 |
| logging slow query             |  0.000006 |
| logging slow query             |  0.000010 |
| cleaning up                    |  0.000010 |
+--------------------------------+-----------+

Thanks for the help.

SHOW CREATE TABLES

SHOW CREATE TABLE customer;

 customer | CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
  `orders` int(11) DEFAULT NULL,
  `country` text COLLATE utf8_unicode_ci,
  `last_date_order` datetime DEFAULT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastname` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `male` tinyint(1) DEFAULT NULL,
  `gender_probability` decimal(10,2) DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=45672 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

SHOW CREATE TABLE order_2016;

order_2016 | CREATE TABLE `order_2016` (
  `id` bigint(12) NOT NULL,
  `name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `created_at_day` date DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `financial_status` enum('pending','authorized','paid','partially_paid','refunded','partially_refunded','voided') COLLATE utf8_unicode_ci DEFAULT NULL,
  `accepts_marketing` enum('yes','no') COLLATE utf8_unicode_ci DEFAULT NULL,
  `subtotal` decimal(6,2) DEFAULT NULL,
  `shipping` decimal(3,2) DEFAULT NULL,
  `total` decimal(6,2) DEFAULT NULL,
  `discount_code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `discount_amount` decimal(6,2) DEFAULT NULL,
  `billing_country` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipping_country` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipping_province` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipping_phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipping_city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipping_zip` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `cancel_reason` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `customer_relacional_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `paid_at` (`paid_at`),
  KEY `created_at_day` (`created_at_day`),
  KEY `index_customer` (`customer_relacional_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Updated EXPLAIN

id select_type table type  possible_keys  key            key_len ref          rows    Extra
1  SIMPLE      c     index NULL           email          212     NULL         1108494 Using index; Using temporary; Using filesort
1  SIMPLE      o     ref   index_customer index_customer 4       hawkers.c.id 1
1

Unless mysql is different from mssql I don't think that is the correct query

SELECT c.id AS 'id', count(o.customer_relacional_id) orders, o.created_at

FROM customer c
LEFT JOIN order_2016 o
ON (o.customer_relacional_id = c.id)
GROUP BY o.customer_relacional_id  
ORDER BY o.created_at  DESC  

this turns it into a regular join

GROUP BY o.customer_relacional_id    
ORDER BY o.created_at  DESC  

try this
but I am not getting this o.created_at
it should be in some type of aggregate
do you mean max(o.created_at) ?

SELECT c.id AS 'id'
     , count(o.customer_relacional_id) ordersCount
     , max(o.created_at) as lastDate
FROM customer c
LEFT JOIN order_2016 o
ON (o.customer_relacional_id = c.id)
GROUP BY c.id 

If it does not work just let me know and I will delete

|improve this answer|||||
  • Aggregates tend to get inflated values when used with JOIN. – Rick James Jun 23 '16 at 5:44
0

You can probably improve your Select by doing an early aggregation, i.e. group before the join:

SELECT c.id AS 'id'
  ,COALESCE(o.orders, 0) AS orders
  ,o.created_at 
FROM customer c 
LEFT JOIN
 (
   SELECT customer_relacional_id, COUNT(*) AS orders, 
      MIN(created_at) AS created_at -- or MAX
   FROM order_2016
   GROUP BY customer_relacional_id
 ) AS  o 
ON (o.customer_relacional_id = c.id) 
ORDER BY o.created_at DESC

Decent optimizer apply this technique automatically, but I doubt that MySQL is smart enough.

|improve this answer|||||
  • This query is taking too long – Victor Company Jun 11 '16 at 18:42
  • What version are you running? – Rick James Jun 23 '16 at 5:48
0

Before version 5.6, the optimizer did not know how to automatically add an index to a subquery. Temp tables are (often) the workaround.

CREATE TEMPORARY TABLE o (
    PRIMARY KEY(customer_relacional_id)
) ENGINE=InnoDB
    SELECT customer_relacional_id,
           COUNT(*) AS orders, 
           MIN(created_at) AS created_at
        FROM order_2016
        GROUP BY customer_relacional_id;
SELECT c.id,
       COALESCE(o.orders, 0) AS orders,
       o.created_at 
    FROM customer c 
    LEFT JOIN o  ON o.customer_relacional_id = c.id
    ORDER BY o.created_at DESC;

This would probably be beneficial:

INDEX(customer_relacional_id, created_at)
|improve this answer|||||

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.