Mysql – Any idea why this seemingly-simple query is really slow

Here's the query

SELECT count(*)
FROM registration r 
LEFT JOIN registration r2 ON r.baggage_group_id = r2.baggage_group_id AND r2.team_id IS NULL
WHERE r.event_id = "102577";

It seems pretty simple, but takes over 30 seconds to run sometimes. I think I'm doing everything right: indexes, no temporary table, no filesort, etc. But still no dice.

Here's some info about my data. The table actually has a bunch of other columns, but I didn't think they were relevant so I removed them from this question. I can post the full schema if that's important.

I'm running MySQL Percona Server 5.5.

mysql> SHOW CREATE TABLE registration;
+--------------+-------------------------------+
| Table        | Create Table                  |
+--------------+-------------------------------+
| registration | CREATE TABLE `registration` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `baggage_group_id` bigint(20) DEFAULT NULL,
  `team_id` bigint(20) DEFAULT NULL,
  `event_id` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`id`),
  KEY `event_id_idx` (`event_id`),
  KEY `team_id_idx` (`team_id`),
  KEY `baggage_group_id_idx` (`baggage_group_id`),

  CONSTRAINT `registration_baggage_group_id_baggage_group_id` FOREIGN KEY (`baggage_group_id`) REFERENCES `baggage_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `registration_event_id_event_id` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `registration_team_id_team_id` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=675650 DEFAULT CHARSET=utf8 |
+--------------+-----------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> SELECT COUNT(*) FROM registration;
+----------+
| COUNT(*) |
+----------+
|   272478 |
+----------+
1 row in set (0.05 sec)



mysql> EXPLAIN SELECT count(*) FROM registration r LEFT JOIN registration r2 ON r.baggage_group_id = r2.baggage_group_id AND r2.team_id IS NULL WHERE r.event_id = "102577" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
         type: ref
possible_keys: playable_year_id_idx
          key: playable_year_id_idx
      key_len: 9
          ref: const
         rows: 788
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: r2
         type: ref
possible_keys: team_id_idx,baggage_group_id_idx
          key: baggage_group_id_idx
      key_len: 9
          ref: ts_dev.r.baggage_group_id
         rows: 10
        Extra: 
2 rows in set (0.00 sec)



mysql> SHOW INDEXES FROM registration;
+--------------+------------+--------------------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name                                   | Seq_in_index | Column_name           | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| registration |          0 | PRIMARY                                    |            1 | id                    | A         |      290369 |     NULL | NULL   |      | BTREE      |         |               |
| registration |          1 | event_id                                   |            1 | event_id              | A         |        3056 |     NULL | NULL   | YES  | BTREE      |         |               |
| registration |          1 | team_id_idx                                |            1 | team_id               | A         |       48394 |     NULL | NULL   | YES  | BTREE      |         |               |
| registration |          1 | baggage_group_id_idx                       |            1 | baggage_group_id      | A         |       29036 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)