Why is MySQL query using join buffer?

Question

The following query is using the join buffer and I was wondering if someone could explain to me why this is so. Just trying to gain more understanding about mysql and indexing.

mysql> EXPLAIN SELECT events.event_topic_id, event_topic_name, event_topic_image, event_type_name,city_name FROM events
    ->             JOIN event_topic ON event_topic.event_topic_id=events.event_topic_id
    ->             JOIN event_type ON event_type.event_type_id = event_topic.event_type_id
    ->             JOIN locations ON locations.location_id=events.location_id
    ->             JOIN city ON city.city_id=locations.city_id
    ->             WHERE event_date > NOW()
    ->             GROUP BY events.event_topic_id, city.city_id;
+----+-------------+-------------+--------+---------------------------------------+-----------------+---------+--------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys                         | key             | key_len | ref                                  | rows | filtered | Extra                                        |
+----+-------------+-------------+--------+---------------------------------------+-----------------+---------+--------------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | city        | index  | PRIMARY                               | city_name       | 52      | NULL                                 |    6 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | locations   | ref    | PRIMARY,city_id                       | city_id         | 1       | PremiumCONNECT.city.city_id          |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | events      | ref    | location_id,event_topic_id,event_date | location_id     | 2       | PremiumCONNECT.locations.location_id |    3 |   100.00 | Using where                                  |
|  1 | SIMPLE      | event_type  | index  | PRIMARY                               | event_type_name | 52      | NULL                                 |    2 |   100.00 | Using index; Using join buffer               |
|  1 | SIMPLE      | event_topic | eq_ref | PRIMARY,event_type_id                 | PRIMARY         | 1       | PremiumCONNECT.events.event_topic_id |    1 |   100.00 | Using where                                  |
+----+-------------+-------------+--------+---------------------------------------+-----------------+---------+--------------------------------------+------+----------+----------------------------------------------+

Events table:

CREATE TABLE `events` (
  `event_id` smallint(8) unsigned NOT NULL AUTO_INCREMENT,
  `location_id` smallint(3) unsigned NOT NULL,
  `event_date` datetime NOT NULL,
  `event_topic_id` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`event_id`),
  KEY `location_id` (`location_id`),
  KEY `event_topic_id` (`event_topic_id`),
  KEY `event_date` (`event_date`),
  CONSTRAINT `events_ibfk_2` FOREIGN KEY (`event_topic_id`) REFERENCES `event_topic` (`event_topic_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `events_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=latin1

Event topic table:

CREATE TABLE `event_topic` (
  `event_topic_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `event_topic_name` varchar(100) DEFAULT NULL,
  `event_topic_description` text NOT NULL,
  `event_topic_cost` decimal(7,2) DEFAULT NULL,
  `event_type_id` tinyint(3) unsigned NOT NULL,
  `event_topic_clickthrough` tinytext,
  `event_topic_length` varchar(6) NOT NULL,
  `event_topic_image` varchar(41) DEFAULT NULL,
  `event_topic_image_md5` char(32) NOT NULL,
  PRIMARY KEY (`event_topic_id`),
  KEY `event_type_id` (`event_type_id`),
  KEY `topic_image_sha1` (`event_topic_image_md5`),
  CONSTRAINT `event_topic_ibfk_1` FOREIGN KEY (`event_type_id`) REFERENCES `event_type` (`event_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1

Event type table:

CREATE TABLE `event_type` (
  `event_type_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `event_type_name` varchar(50) NOT NULL,
  `conf_email` text,
  PRIMARY KEY (`event_type_id`),
  KEY `event_type_name` (`event_type_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

Locations table:

CREATE TABLE `locations` (
  `location_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `location_name` varchar(50) NOT NULL,
  `location_address` tinytext NOT NULL,
  `location_capacity` smallint(6) NOT NULL,
  `city_id` tinyint(3) unsigned NOT NULL,
  `gps_coords` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`location_id`),
  KEY `city_id` (`city_id`),
  CONSTRAINT `locations_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

Cities table:

CREATE TABLE `city` (
  `city_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  PRIMARY KEY (`city_id`),
  UNIQUE KEY `city_name` (`city_name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 

Answer 1

Try using:

"STRAIGHT_JOIN" and "FORCE INDEX":
EXPLAIN SELECT events.event_topic_id, event_topic_name, event_topic_image, event_type_name,city_name FROM events
    ->             straight_join event_topic force index(primary) ON event_topic.event_topic_id=events.event_topic_id
    ->             straight_join event_type force index(primary) ON event_type.event_type_id = event_topic.event_type_id
    ->             straight_join locations force index(primary) ON locations.location_id=events.location_id
    ->             straight_join city force index(primary) ON city.city_id=locations.city_id
    ->             WHERE event_date > NOW()
    ->             GROUP BY events.event_topic_id, city.city_id;

BTW, using a join buffer is not good. It means that you need to improve or reference to correct index.

Answer 2

As it says in 'http://dev.mysql.com/doc/refman/5.1/en/explain-output.html': "Tables from earlier joins are read in portions into the join buffer, and then their rows are used from the buffer to perform the join with the current table."

So in your case, you had already joined event_topic, so the optimizer was able to use event_topic content from the join buffer.

Using a buffer is a good thing; you probably noticed the undesirable "Using temporary; Using filesort" on the first line of EXPLAIN output, which is probably from the GROUP BY and is probably unavoidable in this case.

By the way, will you run into problems with the "UNIQUE" constraint on city_name? I'm thinking of Springfield (two in New Jersey), Washington, Greenville, etc.