title: Why is MySQL query using join buffer?
body: <p>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.</p>
  
  <pre><code>mysql&gt; EXPLAIN SELECT events.event_topic_id, event_topic_name, event_topic_image, event_type_name,city_name FROM events
      -&gt;             JOIN event_topic ON event_topic.event_topic_id=events.event_topic_id
      -&gt;             JOIN event_type ON event_type.event_type_id = event_topic.event_type_id
      -&gt;             JOIN locations ON locations.location_id=events.location_id
      -&gt;             JOIN city ON city.city_id=locations.city_id
      -&gt;             WHERE event_date &gt; NOW()
      -&gt;             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                                  |
  +----+-------------+-------------+--------+---------------------------------------+-----------------+---------+--------------------------------------+------+----------+----------------------------------------------+
  </code></pre>
  
  <p>Events table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Event topic table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Event type table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Locations table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Cities table:</p>
  
  <pre><code>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 
  </code></pre>