1

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!

  • Perhaps this is a blocking-related problem? Percona has an interesting article about how to troubleshoot that problem (the article is several years old, but may still help). – Max Vernon Mar 22 '16 at 15:20
  • You wrote "query that runs in 2 identical environments" - your environments are not identical. I've seen all too many companies where test != prod. Focus on those differences. It looks to me that you're testing on a system which is doing nothing else and on prod, the system is a working one: constantly being hit by other queries. If you're certain that CPU, RAM and disk config are the same, then that's your issue. – Vérace Mar 22 '16 at 15:53
  • yeah I should've specified it better. I meant it's the same version of mysql, same database, almost the same data (mysqldump from prod to qa), etc.. – snakeatd Mar 22 '16 at 16:01
0

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.)

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.