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      |         |               | +-------------+------------+---------------------------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+