0

I have a Google cloud SQL instance with 1 CPU and 3.75GB RAM running MySQL 5.7. The following two queries, both of which involve a common table, have drastic performance differences and I cannot figure out why.

Query 1

SELECT l.*, count(n.id) AS nudges_count
FROM lists l LEFT JOIN nudges n ON n.list_uuid = l.uuid
GROUP BY l.id

Results: 439 rows in set (0.77 sec)

Explain output

+----+-------------+-------+------------+-------+-----------------------------+---------------------------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys               | key                       | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-----------------------------+---------------------------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | l     | NULL       | index | PRIMARY,index_lists_on_uuid | PRIMARY                   | 4       | NULL          |  439 |   100.00 | NULL        |
|  1 | SIMPLE      | n     | NULL       | ref   | index_nudges_on_list_uuid   | index_nudges_on_list_uuid | 109     | nudges.l.uuid | 2430 |   100.00 | Using index |
+----+-------------+-------+------------+-------+-----------------------------+---------------------------+---------+---------------+------+----------+-------------+

Query 2

SELECT e.*, count(n.id) AS nudges_count
FROM engagements e LEFT JOIN nudges n ON n.engagement_uuid = e.uuid
GROUP BY e.id

Results: 45 rows in set (17.57 sec)

Explain output

+----+-------------+-------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+------+---------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                                                                                                                                              | key                             | key_len | ref  | rows    | filtered | Extra                                                           |
+----+-------------+-------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+------+---------+----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | ALL   | PRIMARY,index_engagements_on_uuid,index_engagements_on_engagement_plan_uuid,index_engagements_on_template_uuid,index_engagements_on_parent_engagement_uuid | NULL                            | NULL    | NULL |      45 |   100.00 | Using temporary; Using filesort                                 |
|  1 | SIMPLE      | n     | NULL       | index | NULL                                                                                                                                                       | index_nudges_on_engagement_uuid | 109     | NULL | 1081609 |   100.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+------+---------+----------+-----------------------------------------------------------------+

Index info

+--------+------------+---------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+---------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| nudges |          0 | PRIMARY                         |            1 | id              | A         |     1081609 |     NULL | NULL   |      | BTREE      |         |               |
| nudges |          1 | index_nudges_on_uuid            |            1 | uuid            | A         |     1081609 |     NULL | NULL   | YES  | BTREE      |         |               |
| nudges |          1 | index_nudges_on_list_uuid       |            1 | list_uuid       | A         |         445 |     NULL | NULL   | YES  | BTREE      |         |               |
| nudges |          1 | index_nudges_on_portfolio_uuid  |            1 | portfolio_uuid  | A         |      318731 |     NULL | NULL   | YES  | BTREE      |         |               |
| nudges |          1 | index_nudges_on_message_sid     |            1 | message_sid     | A         |      713372 |     NULL | NULL   | YES  | BTREE      |         |               |
| nudges |          1 | index_nudges_on_engagement_uuid |            1 | engagement_uuid | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| nudges |          1 | index_nudges_on_phone_number    |            1 | phone_number    | A         |      305393 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+---------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lists |          0 | PRIMARY             |            1 | id          | A         |         439 |     NULL | NULL   |      | BTREE      |         |               |
| lists |          1 | index_lists_on_uuid |            1 | uuid        | A         |         439 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------------+------------+---------------------------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name                                    | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+---------------------------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| engagements |          0 | PRIMARY                                     |            1 | id                     | A         |          45 |     NULL | NULL   |      | BTREE      |         |               |
| engagements |          1 | index_engagements_on_uuid                   |            1 | uuid                   | A         |          45 |     NULL | NULL   | YES  | BTREE      |         |               |
| engagements |          1 | index_engagements_on_engagement_plan_uuid   |            1 | engagement_plan_uuid   | A         |          43 |     NULL | NULL   | YES  | BTREE      |         |               |
| engagements |          1 | index_engagements_on_template_uuid          |            1 | template_uuid          | A         |          26 |     NULL | NULL   | YES  | BTREE      |         |               |
| engagements |          1 | index_engagements_on_parent_engagement_uuid |            1 | parent_engagement_uuid | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+---------------------------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

As mentioned in a later comment, the issue was resolved after the database was re-imported, although the underlying cause is still unknown.

Below is the explain for query 2 after the re-import.

    +----+-------------+-------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+---------------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                                                                                                                                              | key                             | key_len | ref           | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+---------------+-------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | index | PRIMARY,index_engagements_on_uuid,index_engagements_on_engagement_plan_uuid,index_engagements_on_template_uuid,index_engagements_on_parent_engagement_uuid | PRIMARY                         | 8       | NULL          |    42 |   100.00 | NULL        |
|  1 | SIMPLE      | n     | NULL       | ref   | index_nudges_on_engagement_uuid                                                                                                                            | index_nudges_on_engagement_uuid | 109     | nudges.e.uuid | 33545 |   100.00 | Using index |
+----+-------------+-------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+---------------+-------+----------+-------------+
  • You are beginning to see why UUIDs are a performance problem. – Rick James Feb 20 '18 at 23:07
0

The first select needs to read 2430 nudges, the second 1081609, you can see that for yourself in the explain output. Reading more than 10% of records via an index is slower than reading all records sequentially, let alone reading the entire table. It would be interesting to try to avoid using an index on nudges for the second query.

  • This seems crazy but after I re-imported the database, the second query ran in 0.73 sec. – Boris Layvant Feb 16 '18 at 16:23
  • Check if the explain plan has changed. – Gerard H. Pille Feb 16 '18 at 21:17
  • The explain did change; the number of rows for query 2 dropped from 1081609 to 33545. – Boris Layvant Feb 20 '18 at 13:57
  • I added the full explain output to the original question. Apart from the number, the 'possible_keys' changed from null to index_nudges_on_engagement_uuid – Boris Layvant Feb 21 '18 at 18:38
  • Perhaps the index on that column was in an unuseable state, something that can happen on Oracle, but I have no experience with MySQL. Don't let it happen again. ;-) Before the import, the cardinality on that column was only 32. Is that still the same? – Gerard H. Pille Feb 21 '18 at 19:44

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.