title: MySQL Query performance - huge difference in time
body: <p>I have a query that is returning in vastly different amounts of time between 2 datasets. For one set (database A) it returns in a few seconds, for the other (database B)....well I haven't waited long enough yet, but over 10 minutes. I have dumped both of these databases to my local machine where I can reproduce the issue running MySQL 5.1.37.</p>
  
  <p>Curiously, database B is smaller than database A.</p>
  
  <p>A stripped down version of the query that reproduces the problem is:</p>
  
  <pre><code>SELECT * FROM po_shipment ps 
  JOIN po_shipment_item psi USING (ship_id) 
  JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items 
  JOIN po_header ph ON pa.hdr_id = ph.hdr_id 
  LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
  LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
  LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
  LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
  LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
  LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
  WHERE ps.eta &gt;= '2010-03-22'
  GROUP BY ps.ship_id
  LIMIT 100;
  </code></pre>
  
  <p>The EXPLAIN query plan for the first database (A) that returns in ~2 seconds is:</p>
  
  <pre><code>+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+
  | id | select_type | table | type   | possible_keys                                                                                                                          | key                              | key_len | ref                          | rows | Extra                                        |
  +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+
  |  1 | SIMPLE      | ps    | range  | PRIMARY,IX_ETA_DATE                                                                                                                    | IX_ETA_DATE                      | 4       | NULL                         |  174 | Using where; Using temporary; Using filesort | 
  |  1 | SIMPLE      | ev0   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev1   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev2   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev3   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev4   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev5   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | psi   | ref    | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1                                                              | IX_po_shipment_item_po_shipment1 | 4       | UNIVIS_PROD.ps.ship_id       |    1 |                                              | 
  |  1 | SIMPLE      | pa    | ref    | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | FK_po_alloc_po_shipment1         | 4       | UNIVIS_PROD.psi.ship_id      |    5 | Using where                                  | 
  |  1 | SIMPLE      | ph    | eq_ref | PRIMARY,IX_HDR_ID                                                                                                                      | PRIMARY                          | 4       | UNIVIS_PROD.pa.hdr_id        |    1 |                                              | 
  +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+
  </code></pre>
  
  <p>The EXPLAIN query plan for the second database (B) that returns in >600 seconds is:</p>
  
  <pre><code>+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+
  | id | select_type | table | type   | possible_keys                                                                                                                          | key                              | key_len | ref                            | rows | Extra                                        |
  +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+
  |  1 | SIMPLE      | ps    | range  | PRIMARY,IX_ETA_DATE                                                                                                                    | IX_ETA_DATE                      | 4       | NULL                           |   38 | Using where; Using temporary; Using filesort | 
  |  1 | SIMPLE      | psi   | ref    | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1                                                              | IX_po_shipment_item_po_shipment1 | 4       | UNIVIS_DEV01.ps.ship_id        |    1 |                                              | 
  |  1 | SIMPLE      | ev0   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.psi.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev1   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.psi.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev2   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const  |    1 |                                              | 
  |  1 | SIMPLE      | ev3   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.psi.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev4   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.psi.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev5   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const  |    1 |                                              | 
  |  1 | SIMPLE      | pa    | ref    | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | IX_po_alloc_po_shipment_item2    | 4       | UNIVIS_DEV01.ps.ship_id        |    4 | Using where                                  | 
  |  1 | SIMPLE      | ph    | eq_ref | PRIMARY,IX_HDR_ID                                                                                                                      | PRIMARY                          | 4       | UNIVIS_DEV01.pa.hdr_id         |    1 |                                              | 
  +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+
  </code></pre>
  
  <p>When database B is running I can look at the MySQL Administrator and the state remains at "Copying to tmp table" indefinitely. Database A also has this state but for only a second or so.</p>
  
  <p>There are no differences in the table structure, indexes, keys etc between these databases (I have done show create tables and diff'd them).</p>
  
  <p>The sizes of the tables are:</p>
  
  <pre><code>database A:
  po_shipment 1776
  po_shipment_item 1945
  po_alloc 36298
  po_header 71642
  EVENT_TABLE 1608
  
  database B:
  po_shipment 463
  po_shipment_item 470
  po_alloc 3291
  po_header 56149
  EVENT_TABLE 1089
  </code></pre>
  
  <p>Some points to note:</p>
  
  <ul>
  <li>Removing the WHERE clause makes the
  query return &lt; 1 sec.</li>
  <li>Removing the GROUP BY makes the query
  return &lt; 1 sec.</li>
  <li>Removing ev5, ev4, ev3 etc makes the
  query get faster for each one
  removed.</li>
  </ul>
  
  <p><strong>UPDATE</strong> after AJ's answer: 
   - The size of the ship_id is significantly larger on database B (max value = 800002752) than database A (max value = 3489). Given that these are InnoDB tables would changing any buffer help with handling keys of this size? <strong>Further Update</strong> to this: I reduced the size of the keys and re-ANALYZEd but still no change in performance.</p>
  
  <p><strong>UPDATE</strong> desc of EVENT_TABLE:</p>
  
  <p>Note that it is <strong>identical</strong> in both databases</p>
  
  <pre><code>+--------------------+--------------+------+-----+---------+----------------+
  | Field              | Type         | Null | Key | Default | Extra          |
  +--------------------+--------------+------+-----+---------+----------------+
  | EVENT_TABLE_ID     | bigint(20)   | NO   | PRI | NULL    | auto_increment | 
  | EVENT_TYPE         | varchar(10)  | NO   |     | NULL    |                | 
  | TABLE_ID1          | int(11)      | NO   | MUL | NULL    |                | 
  | TABLE_ID2          | int(11)      | YES  |     | NULL    |                | 
  | TABLE_ID3          | int(11)      | YES  |     | NULL    |                | 
  | TABLE_ID4          | int(11)      | YES  |     | NULL    |                | 
  | EVENT_CREATED_DATE | datetime     | NO   |     | NULL    |                | 
  | MESSAGE_REF        | varchar(100) | YES  |     | NULL    |                | 
  +--------------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>And for good measure the SHOW CREATE TABLE EVENT_TABLE:</p>
  
  <p>The only thing to differ in this between databases is the auto increment value</p>
  
  <pre><code>| EVENT_TABLE | CREATE TABLE `EVENT_TABLE` (
    `EVENT_TABLE_ID` bigint(20) NOT NULL AUTO_INCREMENT,
    `EVENT_TYPE` varchar(10) NOT NULL,
    `TABLE_ID1` int(11) NOT NULL,
    `TABLE_ID2` int(11) DEFAULT NULL,
    `TABLE_ID3` int(11) DEFAULT NULL,
    `TABLE_ID4` int(11) DEFAULT NULL,
    `EVENT_CREATED_DATE` datetime NOT NULL,
    `MESSAGE_REF` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`EVENT_TABLE_ID`),
    KEY `IX_EVENT_ID_EVENT_TYPE` (`TABLE_ID1`,`EVENT_TYPE`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1925 DEFAULT CHARSET=utf8 | 
  </code></pre>
  
  <p>Can anyone suggest how to resolve this issue? What have I missed?</p>
  
  <p><strong>UPDATE after question from Michael Holzmann</strong>
  Here is the new Query plans based on his updated STRAIGHT_JOIN query. Note that database B has "Using temporary; Using filesort" whereas now database A doesn't. Could this be due to the long keys or something similar?</p>
  
  <p>database A</p>
  
  <pre><code>+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+-------------+
  | id | select_type | table | type   | possible_keys                                                                                                                          | key                              | key_len | ref                          | rows | Extra       |
  +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+-------------+
  |  1 | SIMPLE      | ps    | index  | PRIMARY,IX_ETA_DATE                                                                                                                    | PRIMARY                          | 4       | NULL                         |  168 | Using where | 
  |  1 | SIMPLE      | ev0   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
  |  1 | SIMPLE      | ev1   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
  |  1 | SIMPLE      | ev2   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
  |  1 | SIMPLE      | ev3   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
  |  1 | SIMPLE      | ev4   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
  |  1 | SIMPLE      | ev5   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |             | 
  |  1 | SIMPLE      | psi   | ref    | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1                                                              | IX_po_shipment_item_po_shipment1 | 4       | UNIVIS_PROD.ps.ship_id       |    1 |             | 
  |  1 | SIMPLE      | pa    | ref    | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | FK_po_alloc_po_shipment_item1    | 8       | UNIVIS_PROD.psi.UID_items    |    6 | Using where | 
  |  1 | SIMPLE      | ph    | eq_ref | PRIMARY,IX_HDR_ID                                                                                                                      | PRIMARY                          | 4       | UNIVIS_PROD.pa.hdr_id        |    1 |             | 
  +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+-------------+
  </code></pre>
  
  <p>database B</p>
  
  <pre><code>+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------------------------------------------+
  | id | select_type | table | type   | possible_keys                                                                                                                          | key                              | key_len | ref                           | rows | Extra                                        |
  +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------------------------------------------+
  |  1 | SIMPLE      | ps    | range  | PRIMARY,IX_ETA_DATE                                                                                                                    | IX_ETA_DATE                      | 4       | NULL                          |   38 | Using where; Using temporary; Using filesort | 
  |  1 | SIMPLE      | ev0   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev1   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev2   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev3   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev4   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev5   | ref    | IX_EVENT_ID_EVENT_TYPE                                                                                                                 | IX_EVENT_ID_EVENT_TYPE           | 36      | UNIVIS_DEV01.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | psi   | ref    | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1                                                              | IX_po_shipment_item_po_shipment1 | 4       | UNIVIS_DEV01.ps.ship_id       |    1 |                                              | 
  |  1 | SIMPLE      | pa    | ref    | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | IX_po_alloc_po_shipment_item2    | 4       | UNIVIS_DEV01.ps.ship_id       |    3 | Using where                                  | 
  |  1 | SIMPLE      | ph    | eq_ref | PRIMARY,IX_HDR_ID                                                                                                                      | PRIMARY                          | 4       | UNIVIS_DEV01.pa.hdr_id        |    1 |                                              | 
  +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------------------------------------------+
  </code></pre>
  
  <p><strong>UPDATE</strong> It's definitely data related. I dumped the data from database A and loaded it into database B using:</p>
  
  <pre><code>SELECT * from &lt;table&gt; into outfile &lt;file&gt;
  </code></pre>
  
  <p>and </p>
  
  <pre><code>LOAD DATA INFILE &lt;file&gt; into table &lt;table&gt;
  </code></pre>
  
  <p>Then the database B query runs quickly - ie. as fast as database A. Any ideas on how to diagnose what could be wrong with the data??</p>
  
  <p><strong>UPDATE</strong> @newtover: 
  From database A:</p>
  
  <pre><code>+-----------------+---------------------+
  | eta_selectivity | ship_id_selectivity |
  +-----------------+---------------------+
  |          0.0693 |              1.0000 | 
  +-----------------+---------------------+
  1 row in set (0.02 sec)
  </code></pre>
  
  <p>From database B (the bad one)</p>
  
  <pre><code>+-----------------+---------------------+
  | eta_selectivity | ship_id_selectivity |
  +-----------------+---------------------+
  |          0.1814 |              1.0000 | 
  +-----------------+---------------------+
  1 row in set (0.02 sec)
  </code></pre>
  
  <p>And the show create for po_shipment:</p>
  
  <pre><code>| po_shipment | CREATE TABLE `po_shipment` (
    `ship_id` int(11) NOT NULL DEFAULT '0',
    `ship_type` varchar(16) DEFAULT NULL,
    `foreign_agent` varchar(16) DEFAULT NULL,
    `agent_ref` varchar(16) DEFAULT NULL,
    `exporter_code` varchar(30) DEFAULT NULL,
    `importer_code` varchar(30) DEFAULT NULL,
    `carrier_code` varchar(30) DEFAULT NULL,
    `exporter_name` varchar(50) DEFAULT NULL,
    `importer_name` varchar(50) DEFAULT NULL,
    `carrier_name` varchar(50) DEFAULT NULL,
    `receipt` varchar(30) DEFAULT NULL,
    `pol_aol` varchar(50) DEFAULT NULL,
    `pod_aod` varchar(30) DEFAULT NULL,
    `final_dest` varchar(50) DEFAULT NULL,
    `vessel_flno` varchar(30) DEFAULT NULL,
    `ets` date DEFAULT NULL,
    `eta` date DEFAULT NULL,
    `pieces` int(11) DEFAULT '0',
    `weight` decimal(17,2) DEFAULT '0.00',
    `volume` decimal(17,2) DEFAULT '0.00',
    `marks` varchar(500) DEFAULT NULL,
    `goods_desc` varchar(500) DEFAULT NULL,
    `ship_terms` varchar(16) DEFAULT NULL,
    `ship_terms_desc` varchar(50) DEFAULT NULL,
    `house_hawb` varchar(30) DEFAULT NULL,
    `ocean_mawb` varchar(30) DEFAULT NULL,
    `booking_date` date DEFAULT NULL,
    `expected_cargo` date DEFAULT NULL,
    `mfrt_jobdisp` varchar(30) DEFAULT NULL,
    `ship_complete` date DEFAULT NULL,
    `user_id` varchar(30) DEFAULT NULL,
    `receipt_desc` varchar(60) DEFAULT NULL,
    `fin_dest_desc` varchar(60) DEFAULT NULL,
    `pol_aol_desc` varchar(60) DEFAULT NULL,
    `pod_aod_desc` varchar(60) DEFAULT NULL,
    `exporter_ref` varchar(26) DEFAULT NULL,
    `carrier_ref` varchar(26) DEFAULT NULL,
    `terms_conds` date DEFAULT NULL,
    `last_amended` date DEFAULT NULL,
    `user_amended` varchar(30) DEFAULT NULL,
    `package_type` varchar(24) DEFAULT NULL,
    `ext_cancelled` tinyint(1) NOT NULL DEFAULT '0',
    `ext_goh` tinyint(1) NOT NULL DEFAULT '0',
    `ext_arrival_date` date DEFAULT NULL,
    `ext_booking_ref` varchar(255) DEFAULT NULL,
    `ext_dc_booked_delivery_date` date DEFAULT NULL,
    `ext_dc_booked_delivery_time` varchar(10) DEFAULT NULL,
    `ext_comments` text,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `last_amended_time` int(10) DEFAULT NULL,
    `last_amended_uni` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`ship_id`),
    KEY `IX_ETA_DATE` (`eta`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
  </code></pre>
  
  <p><strong>UPDATE</strong> @chris_I
  If I strip the query down by removing all other joins aside from EVENT_TABLE I get the same performance (ie. crappy)</p>
  
  <pre><code>SELECT * FROM po_shipment ps 
  LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
  LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
  LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
  LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
  LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
  LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
  WHERE ps.eta &gt;= '2010-03-22'
  GROUP BY ps.ship_id
  LIMIT 100;
  </code></pre>
  
  <p><strong>UPDATE</strong> @Marcus Adams: 
  Query for plans you have asked for with inner joins removed:</p>
  
  <pre><code>SELECT * FROM po_shipment ps 
  LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
  LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
  LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
  LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
  LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
  LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
  WHERE ps.eta &gt;= '2010-03-22'
  GROUP BY ps.ship_id
  LIMIT 100;
  </code></pre>
  
  <p>Query Plan from database A (responds in 0.35s)</p>
  
  <pre><code>+----+-------------+-------+-------+------------------------+------------------------+---------+------------------------------+------+----------------------------------------------+
  | id | select_type | table | type  | possible_keys          | key                    | key_len | ref                          | rows | Extra                                        |
  +----+-------------+-------+-------+------------------------+------------------------+---------+------------------------------+------+----------------------------------------------+
  |  1 | SIMPLE      | ps    | range | IX_ETA_DATE            | IX_ETA_DATE            | 4       | NULL                         |  174 | Using where; Using temporary; Using filesort | 
  |  1 | SIMPLE      | ev0   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev1   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev2   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev3   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev4   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  |  1 | SIMPLE      | ev5   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36      | UNIVIS_PROD.ps.ship_id,const |    1 |                                              | 
  +----+-------------+-------+-------+------------------------+------------------------+---------+------------------------------+------+----------------------------------------------
  </code></pre>
  
  <p>Query Plan from database B (doesn't respond in time it takes to make a cup of tea)</p>
  
  <pre><code>    +----+-------------+-------+-------+------------------------+------------------------+---------+-------------------------------+------+----------------------------------------------+
  | id | select_type | table | type  | possible_keys          | key                    | key_len | ref                           | rows | Extra                                        |
  +----+-------------+-------+-------+------------------------+------------------------+---------+-------------------------------+------+----------------------------------------------+
  |  1 | SIMPLE      | ps    | range | IX_ETA_DATE            | IX_ETA_DATE            | 4       | NULL                          |   38 | Using where; Using temporary; Using filesort | 
  |  1 | SIMPLE      | ev0   | ref   | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36    
  </code></pre>