title: How to improve performance of query with two inner joins?
body: <p>I have 3 tables :</p>
  
  <pre><code>CREATE TABLE `t_event` (
    `id` int(10) NOT NULL auto_increment,
    `title` varchar(100) NOT NULL,
    `kind` int(10) NOT NULL,
    `type` int(10) NOT NULL,
    `short_desc` varchar(500) default NULL,
    `long_desc` varchar(1500) default NULL,
    `location` int(10) NOT NULL,
    `price` decimal(11,0) NOT NULL,
    `currency` int(11) NOT NULL default '1',
    `remark_price` varchar(250) default NULL,
    `remark_prerequisite` varchar(250) default NULL,
    `date_start` date NOT NULL,
    `date_end` date default NULL,
    `date_remark` varchar(300) default NULL,
    `time_start` time default NULL,
    `time_end` time default NULL,
    `remark_time` varchar(50) default NULL,
    `leader` int(50) NOT NULL,
    `leader2` int(100) NOT NULL,
    `eve_contact_name` varchar(50) default NULL,
    `eve_contact_phone` varchar(50) default NULL,
    `eve_contact_email` varchar(50) default NULL,
    `eve_contact_url` varchar(150) default NULL,
    `eve_image_path` varchar(250) default NULL,
    `provider` int(10) default NULL,
    `timestamp` datetime NOT NULL,
    `last_change` datetime NOT NULL default '0000-00-00 00:00:00',
    `quality` int(10) default NULL,
    `min_number` int(10) NOT NULL,
    `max_number` int(10) NOT NULL,
    `active_for_reservation` tinyint(1) NOT NULL,
    `cancellation_day1` int(10) NOT NULL,
    `cancellation_day2` int(10) NOT NULL,
    `cancellation_fee1` varchar(255) NOT NULL,
    `cancellation_fee2` varchar(255) NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `FK_t_event_t_event_kind` (`kind`),
    KEY `FK_t_event_t_event_type` (`type`),
    KEY `FK_t_event_t_location` (`location`),
    KEY `FK_t_event_t_currency` (`currency`),
    KEY `FK_t_event_t_leader` (`leader`),
    KEY `FK_t_event_t_provider` (`provider`),
    KEY `FK_t_event_t_quality` (`quality`),
    CONSTRAINT `FK_t_event_t_currency` FOREIGN KEY (`currency`) REFERENCES `t_currency` (`id`),
    CONSTRAINT `FK_t_event_t_event_kind` FOREIGN KEY (`kind`) REFERENCES `t_event_kind` (`id`),
    CONSTRAINT `FK_t_event_t_event_type` FOREIGN KEY (`type`) REFERENCES `t_event_type` (`id`),
    CONSTRAINT `FK_t_event_t_leader` FOREIGN KEY (`leader`) REFERENCES `t_leader` (`id`),
    CONSTRAINT `FK_t_event_t_location` FOREIGN KEY (`location`) REFERENCES `t_location` (`id`),
    CONSTRAINT `FK_t_event_t_provider` FOREIGN KEY (`provider`) REFERENCES `t_provider` (`id`),
    CONSTRAINT `FK_t_event_t_quality` FOREIGN KEY (`quality`) REFERENCES `t_quality` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=8432 DEFAULT CHARSET=latin1
  
  CREATE TABLE `t_location` (
    `id` int(10) NOT NULL auto_increment,
    `loc_name` varchar(50) NOT NULL,
    `loc_detail` varchar(50) default NULL,
    `loc_adress1` varchar(50) NOT NULL,
    `loc_adress2` varchar(50) default NULL,
    `loc_country` int(50) NOT NULL default '1',
    `loc_zip` varchar(50) NOT NULL,
    `loc_loc` varchar(50) NOT NULL,
    `loc_shortdesc` varchar(250) default NULL,
    `loc_contact_name` varchar(250) default NULL,
    `loc_contact_gender` int(10) default NULL,
    `loc_contact_phone` varchar(250) default NULL,
    `loc_contact_email` varchar(250) default NULL,
    `loc_contact_url` varchar(250) default NULL,
    `loc_image_path` varchar(250) default NULL,
    `latitude` varchar(100) default NULL,
    `longitude` varchar(100) default NULL,
    `created` datetime NOT NULL,
    `last_change` datetime NOT NULL default '0000-00-00 00:00:00',
    `provider` int(10) NOT NULL default '1',
    PRIMARY KEY  (`id`),
    UNIQUE KEY `id` USING BTREE (`id`),
    KEY `FK_t_location_t_country` (`loc_country`),
    KEY `FK_t_location_t_gender` (`loc_contact_gender`),
    KEY `FK_t_location_t_provider` (`provider`),
    CONSTRAINT `FK_t_location_t_country` FOREIGN KEY (`loc_country`) REFERENCES `t_country`(`id`),
    CONSTRAINT `FK_t_location_t_provider` FOREIGN KEY (`provider`) REFERENCES `t_provider` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1287 DEFAULT CHARSET=latin1
  
  CREATE TABLE `t_dates` (
    `id` int(10) NOT NULL auto_increment,
    `events_id` int(10) NOT NULL,
    `events_start_date` date NOT NULL,
    `events_end_date` date NOT NULL,
    PRIMARY KEY  (`id`),
    UNIQUE KEY `IND_id` (`id`),
    KEY `IND_events_id` (`events_id`),
    CONSTRAINT `t_dates_ibfk_1` FOREIGN KEY (`events_id`) REFERENCES `t_event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB AUTO_INCREMENT=32048 DEFAULT CHARSET=latin1
  </code></pre>
  
  <hr>
  
  <p>My Query is :</p>
  
  <pre><code>SELECT e.*,I.* ,d.*
  FROM t_event AS e
  INNER JOIN t_location AS I ON   I.id = e.location
  INNER JOIN t_dates  AS d ON  d.events_id  = e.id
  ;
  </code></pre>
  
  <p>this query take 90s to be executed and return = 27727</p>
  
  <p>The PROFILE command show that section "sending data" take almost the time of execution.
  The EXPLAIN command is the following : </p>
  
  <pre><code>+----+------------+------+------+----------------------------+--------------------+---------+-----------+-------+-------+
  | id | select_type | table | type | possible_keys               | key                | key_len | ref       | rows | Extra |
  +----+------------+------+------+----------------------------+--------------------+---------+-----------+-------+-------+
  |  1 | SIMPLE     | I    | ALL | PRIMARY,id                 | NULL               | NULL    | NULL      |  1143 |       |
  |  1 | SIMPLE     | e    | ref  | PRIMARY,FK_t_event_t_location | FK_t_event_t_location | 4       | wu_db.I.id |     4 |       |
  |  1 | SIMPLE     | d    | ref  | IND_events_id               | IND_events_id       | 4       | wu_db.e.id |     3 |       |
  +----+------------+------+------+----------------------------+--------------------+---------+-----------+-------+-------+
  </code></pre>
  
  <p>My point of view is that the big number of column is responsible of this slowdown but even when I write  "SELECT e.id, I.events_id, d.id" it still take 16 s.</p>
  
  <p>I think that I have to rewrite the query with LIMIT and OFFSET clause, what do you think?</p>
  
  <p>number of records for each tables :</p>
  
  <ul>
  <li>t_event = 7991</li>
  <li>t_location = 1086</li>
  <li>t_dates = 27727</li>
  </ul>