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