1

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)
7
  • Did you try SELECT count(*) FROM registration r LEFT JOIN registration r2 ON r.baggage_group_id = r2.baggage_group_id WHERE r.event_id = "102577" AND r2.team_id IS NULL;
    – da_didi
    Commented Apr 17, 2014 at 3:56
  • @da_didi that is a different query (and would result in a different count). Commented Apr 17, 2014 at 7:23
  • But seems like a valid question: What exactly do you want the query to do @lyoshenka? Commented Apr 17, 2014 at 7:36
  • Can't see anything obvious. There is something we are missing. Does it always run slowly or is it inconsistent? Commented Apr 17, 2014 at 11:15
  • @AndrewBrennan it's always slow the first time I run it, even for different event_ids. Subsequent runs are instant, pretty sure that's because it gets cached.
    – Alex Grin
    Commented Apr 17, 2014 at 12:59

1 Answer 1

2

Please modify the query:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.