Query taking more than 5 minutes to execute

Question

I've a query that runs in 2 identical environments: Production and QA. In QA server, it takes around 6 seconds and in production it takes 5 minutes or more!

Query is as follows:

    mysql> SELECT  s.id_shopper, 
      ( SELECT  count(*)
            FROM  shopper s2
            JOIN  orders o2 ON o2.id_shopper = s2.id_shopper
              AND  o2.order_status in ('V','X')
            WHERE  o2.id_shopper = s.id_shopper
      )as num_ordenes, s.upi,
        s.email, s.first_name, s.last_name1, s.last_name2, s.shtype,
        s.active, s.shtype, s.registered_on AS regdate, s.upi_type AS upi_type,
        s.gender, s.birthday, s.phtype1, s.phacode1, s.phone1,
        s.phtype2, s.phacode2, s.phone2, s.sellercode, s.mail_offers,
        c.name as pais, r.name as region, ct.name as ciudad, a.name as area,
        s.postcode, s.street, s.street_number,
        concat(r.name, " - ", a.name) as comuna_compra
    FROM  shopper s
    LEFT JOIN  orders o ON o.id_shopper = s.id_shopper
    LEFT JOIN  country c ON c.id_country = s.id_country
    LEFT JOIN  region r ON r.id_region = s.id_region
    LEFT JOIN  city ct ON ct.id_city = s.id_city
    LEFT JOIN  area a ON a.id_area = s.id_area
    GROUP BY  s.id_shopper, s.upi, s.email, s.first_name, s.last_name1,
        s.last_name2, s.shtype, s.active, s.registered_on, s.upi_type,
        s.phtype1, s.phacode1, s.phone1, s.phtype2, s.phacode2,
        s.phone2, s.mail_offers;

Explain is the same for both:

    mysql> explain SELECT s.id_shopper,(SELECT count(*)FROM shopper s2 JOIN orders o2 ON o2.id_shopper = s2.id_shopper AND o2.order_status in ('V','X') WHERE o2.id_shopper = s.id_shopper)as num_ordenes, s.upi, s.email, s.first_name, s.last_name1, s.last_name2, s.shtype, s.active, s.shtype, s.registered_on AS regdate, s.upi_type AS upi_type, s.gender, s.birthday, s.phtype1, s.phacode1, s.phone1, s.phtype2, s.phacode2, s.phone2, s.sellercode, s.mail_offers, c.name as pais, r.name as region, ct.name as ciudad, a.name as area, s.postcode, s.street, s.street_number, concat(r.name," - ", a.name) as comuna_compra FROM shopper s LEFT JOIN orders o ON o.id_shopper = s.id_shopper LEFT JOIN country c ON c.id_country = s.id_country LEFT JOIN region r ON r.id_region = s.id_region LEFT JOIN city ct ON ct.id_city = s.id_city LEFT JOIN area a ON a.id_area = s.id_area GROUP BY s.id_shopper, s.upi, s.email, s.first_name, s.last_name1, s.last_name2, s.shtype, s.active, s.registered_on, s.upi_type, s.phtype1, s.phacode1, s.phone1, s.phtype2, s.phacode2, s.phone2, s.mail_offers;
+----+--------------------+-------+--------+-----------------+-----------------+---------+-----------------------+------+---------------------------------+
| id | select_type        | table | type   | possible_keys   | key             | key_len | ref                   | rows | Extra                           |
+----+--------------------+-------+--------+-----------------+-----------------+---------+-----------------------+------+---------------------------------+
|  1 | PRIMARY            | s     | ALL    | NULL            | NULL            | NULL    | NULL                  | 3548 | Using temporary; Using filesort |
|  1 | PRIMARY            | o     | ref    | orders_FKIndex1 | orders_FKIndex1 | 4       | rosen_ar.s.id_shopper |    4 | Using index                     |
|  1 | PRIMARY            | c     | eq_ref | PRIMARY         | PRIMARY         | 4       | rosen_ar.s.id_country |    1 |                                 |
|  1 | PRIMARY            | r     | eq_ref | PRIMARY         | PRIMARY         | 4       | rosen_ar.s.id_region  |    1 |                                 |
|  1 | PRIMARY            | ct    | eq_ref | PRIMARY         | PRIMARY         | 4       | rosen_ar.s.id_city    |    1 |                                 |
|  1 | PRIMARY            | a     | eq_ref | PRIMARY         | PRIMARY         | 4       | rosen_ar.s.id_area    |    1 |                                 |
|  2 | DEPENDENT SUBQUERY | s2    | eq_ref | PRIMARY         | PRIMARY         | 4       | func                  |    1 | Using index                     |
|  2 | DEPENDENT SUBQUERY | o2    | ref    | orders_FKIndex1 | orders_FKIndex1 | 4       | func                  |    4 | Using where                     |
+----+--------------------+-------+--------+-----------------+-----------------+---------+-----------------------+------+---------------------------------+

Indexes from shopper and orders:

mysql> show indexes from shopper;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| shopper |          0 | PRIMARY          |            1 | id_shopper  | A         |        3812 |     NULL | NULL   |      | BTREE      |         |
| shopper |          1 | shopper_FKIndex1 |            1 | id_area     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |
| shopper |          1 | shopper_FKIndex2 |            1 | id_country  | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| shopper |          1 | shopper_FKIndex3 |            1 | id_city     | A         |         190 |     NULL | NULL   |      | BTREE      |         |
| shopper |          1 | shopper_tbfk_1   |            1 | id_ticket   | A         |          40 |     NULL | NULL   | YES  | BTREE      |         |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> show indexes from orders;
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| orders |          0 | PRIMARY            |            1 | id_order    | A         |       41181 |     NULL | NULL   |      | BTREE      |         |
| orders |          1 | orders_FKIndex1    |            1 | id_shopper  | A         |         496 |     NULL | NULL   |      | BTREE      |         |
| orders |          1 | orders_FKIndex2    |            1 | ep_id_area  | A         |         840 |     NULL | NULL   | YES  | BTREE      |         |
| orders |          1 | fk_orders_tiendas1 |            1 | id_tienda   | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

What could be wrong? Is it because the indexes are set to 1 for very relationship, making it slow? Should I delete the indexes and create them again?

Thanks in advance!

Answer 1

I realise this is an old question, but as it is still here, I thought I would address some of the issues that to me seem to be on the surface.

I do not really have a good explanation why your query runs fine in one environment but not the other. I do see some issues with it that I believe might cause it to run slow.

First of all, this join seems completely redundant:

    LEFT JOIN  orders o ON o.id_shopper = s.id_shopper

Nothing is selected from order, nor does this table participate in other joins. The only effect it probably causes is duplication of shopper rows, because there are likely multiple orders per shopper.

At the same time, the purpose of the GROUP BY appears to be the opposite: de-duplication of the results brought on by the aforementioned join. Without the join, it is not needed either. Therefore, remove the GROUP BY clause as well.

Finally, the correlated subquery in the SELECT clause can be simplified to this:

      ( SELECT  count(*)
            FROM  orders o
           WHERE  o.id_shopper = s.id_shopper
             AND  o.order_status in ('V','X')
      )

You do not need to have an extra instance of shopper there. It is enough to select directly from orders, correlating it with shopper from the outer SELECT, as shown above.

After applying those changes, the full query would look like this:

SELECT  s.id_shopper, 
      ( SELECT  count(*)
            FROM  orders o
           WHERE  o.id_shopper = s.id_shopper
             AND  o.order_status in ('V','X')
      )as num_ordenes, s.upi,
        s.email, s.first_name, s.last_name1, s.last_name2, s.shtype,
        s.active, s.shtype, s.registered_on AS regdate, s.upi_type AS upi_type,
        s.gender, s.birthday, s.phtype1, s.phacode1, s.phone1,
        s.phtype2, s.phacode2, s.phone2, s.sellercode, s.mail_offers,
        c.name as pais, r.name as region, ct.name as ciudad, a.name as area,
        s.postcode, s.street, s.street_number,
        concat(r.name, " - ", a.name) as comuna_compra
    FROM  shopper s
    LEFT JOIN  country c ON c.id_country = s.id_country
    LEFT JOIN  region r ON r.id_region = s.id_region
    LEFT JOIN  city ct ON ct.id_city = s.id_city
    LEFT JOIN  area a ON a.id_area = s.id_area;

Answer 2

Let me explain a problem with the query...

One shopper maps to many orders. From there you get to c.name as pais, r.name as region, ct.name as ciudad, a.name as area. But that means there could be multiple different country, city, etc.

This has two problems:

• It means that the ciudad displayed will be some value picked at random.
• The JOIN explodes the number of rows being looked at, only to have the GROUP BY shrink back down.

I recommend you rethink the requirement of having city, etc, in the resultset. If you remove those columns, and the LEFT JOINs, much of the problem will vanish.

If you need to have city, etc, then rethink what value you will use.

In a related thought... Consider having a Location table which contains those 4 columns, and join to that one table instead of running around to 4 tables.

Back to your question...

• are tables in both environments using InnoDB?
• are the cache sizes the same (innodb_buffer_pool_size)
• is the cache "hot" in both cases? I suspect it is "cold" in QA -- this can easily lead to it being 10 times as slow. (OK, 50x is stretching this answer.)