在表关联查询中，mysql优化器往往会选择结果集小的表，但是往往也会有例外：
SELECT                                                                    ebt.relative_type,                                                ebt.relative_Id,                                                  bu.buyer_photo,                                                   ebt.`business_type`,                                              bu.email,                                                         bu.first_name,                                                    bu.last_name                                                               FROM                                                      `edm$business_task` ebt,                                          `edm$business_task_list` ebtl,                                    `buyer$user` bu,                                                  `edm$country_strategy` ecs                                                                         WHERE ebt.task_id = ebtl.task_id                                  AND ebt.business_type = ebtl.business_type                        AND bu.buyer_id = ebt.relative_id                                 AND ecs.country_id = bu.country_id                                AND ebt.business_type = 23                                       AND ebtl.exec_email = 0                                           AND ecs.delivery_time_point = '1:00'                              AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
执行时间需要三十秒
explain下，以ebt作为驱动表：
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
| id | select_type | table | type   | possible_keys                                          | key                    | key_len | ref                   | rows    | Extra       |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
|  1 | SIMPLE      | ebt   | ref    | PRIMARY,index_business_task_rid,index_business_task_bt | index_business_task_bt | 5       | const                 | 3341380 | Using where |
|  1 | SIMPLE      | bu    | eq_ref | PRIMARY,idx_buyer$user_country_id                      | PRIMARY                | 4       | cfec1.ebt.relative_id |       1 | Using where |
|  1 | SIMPLE      | ebtl  | ref    | index_business_tl_tid,index_business_tl_bt             | index_business_tl_tid  | 5       | cfec1.ebt.task_id     |       1 | Using where |
|  1 | SIMPLE      | ecs   | ref    | fk_strategy_bt                                         | fk_strategy_bt         | 4       | cfec1.bu.country_id   |       1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+------------

查看ebt的结果集（ebt.business_type = 23）
mysql> select  count(*) from `edm$business_task` ebt where ebt.business_type =23 ;
+----------+
| count(*) |
+----------+
|  1749616 |
+----------+
1 row in set (0.87 sec)
ebt表竟然有100多万行记录，然后想办法缩小结果集
我们关注查询这样一些表达式：ebt.business_type = ebtl.business_type AND ebt.business_type = 23然后ebtl.exec_email = 0，TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
是否可以将ebtl作为关联表，ebt.business_type = 23改成ebtl.business_type=23来减少结果集呢？
看如下语句，结果集为22w的样子
mysql> SELECT count(*) FROM `edm$business_task_list` ebtl   WHERE  ebtl.business_type=23 AND ebtl.exec_email = 0  AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
+----------+
| count(*) |
+----------+
|   222669 |
+----------+
看来理论上还是可行了

改写sql语句如下：

mysql> SELECT                                                                    ebt.relative_type,                                                ebt.relative_Id,                                                  bu.buyer_photo,                                                   ebt.`business_type`,                                              bu.email,                                                         bu.first_name,                                                    bu.last_name                                                               FROM                                                      `edm$business_task` ebt,                                          `edm$business_task_list` ebtl,                                    `buyer$user` bu,                                                  `edm$country_strategy` ecs                                                                         WHERE ebt.task_id = ebtl.task_id                                  AND ebt.business_type = ebtl.business_type                        AND bu.buyer_id = ebt.relative_id                                 AND ecs.country_id = bu.country_id                                AND ebtl.business_type = 23                                       AND ebtl.exec_email = 0                                           AND ecs.delivery_time_point = '1:00'                              AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
Empty set (30.73 sec)

结果还是30秒
查看下执行计划：
mysql> explain SELECT                                                                    ebt.relative_type,                                                ebt.relative_Id,                                                  bu.buyer_photo,                                                   ebt.`business_type`,                                              bu.email,                                                         bu.first_name,                                                    bu.last_name                                                               FROM                                                      `edm$business_task` ebt,                                          `edm$business_task_list` ebtl,                                    `buyer$user` bu,                                                  `edm$country_strategy` ecs                                                                         WHERE ebt.task_id = ebtl.task_id                                  AND ebt.business_type = ebtl.business_type                        AND bu.buyer_id = ebt.relative_id                                 AND ecs.country_id = bu.country_id                                AND ebtl.business_type = 23                                       AND ebtl.exec_email = 0                                           AND ecs.delivery_time_point = '1:00'                              AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;                                                                                            
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
| id | select_type | table | type   | possible_keys                                          | key                    | key_len | ref                   | rows    | Extra       |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+
|  1 | SIMPLE      | ebt   | ref    | PRIMARY,index_business_task_rid,index_business_task_bt | index_business_task_bt | 5       | const                 | 3341380 | Using where |
|  1 | SIMPLE      | bu    | eq_ref | PRIMARY,idx_buyer$user_country_id                      | PRIMARY                | 4       | cfec1.ebt.relative_id |       1 | Using where |
|  1 | SIMPLE      | ebtl  | ref    | index_business_tl_tid,index_business_tl_bt             | index_business_tl_tid  | 5       | cfec1.ebt.task_id     |       1 | Using where |
|  1 | SIMPLE      | ecs   | ref    | fk_strategy_bt                                         | fk_strategy_bt         | 4       | cfec1.bu.country_id   |       1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+------------------------+---------+-----------------------+---------+-------------+

还是用原来的ebt作为驱动表（由此可看出MySQL优化器没有自动优化）
那么我的强制使用ebtl作为驱动表，改写sql如下，发现执行时间只有5s：
mysql>  SELECT      ebt.relative_type,        ebt.relative_Id,      bu.buyer_photo,      ebt.`business_type`,    bu.email,    bu.first_name,    bu.last_name     FROM      `edm$business_task_list` ebtl straight_join   `edm$business_task` ebt,     `buyer$user` bu,    `edm$country_strategy` ecs     WHERE ebt.task_id = ebtl.task_id    AND ebt.business_type = ebtl.business_type    AND bu.buyer_id = ebt.relative_id        AND ecs.country_id = bu.country_id     AND ebtl.business_type = 23        AND ebtl.exec_email = 0           AND ecs.delivery_time_point = '1:00'    AND TIMESTAMPDIFF(DAY,ebtl.create_time, '2015-09-14' ) = 0;
Empty set (4.99 sec)
查看执行计划，以ebtl作为了驱动表：
+----+-------------+-------+--------+--------------------------------------------------------+----------------------+---------+-----------------------+---------+-------------+
| id | select_type | table | type   | possible_keys                                          | key                  | key_len | ref                   | rows    | Extra       |
+----+-------------+-------+--------+--------------------------------------------------------+----------------------+---------+-----------------------+---------+-------------+
|  1 | SIMPLE      | ebtl  | ref    | index_business_tl_tid,index_business_tl_bt             | index_business_tl_bt | 10      | const,const           | 3312034 | Using where |
|  1 | SIMPLE      | ebt   | eq_ref | PRIMARY,index_business_task_rid,index_business_task_bt | PRIMARY              | 4       | cfec1.ebtl.task_id    |       1 | Using where |
|  1 | SIMPLE      | bu    | eq_ref | PRIMARY,idx_buyer$user_country_id                      | PRIMARY              | 4       | cfec1.ebt.relative_id |       1 | Using where |
|  1 | SIMPLE      | ecs   | ref    | fk_strategy_bt                                         | fk_strategy_bt       | 4       | cfec1.bu.country_id   |       1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+----------------------+---------+-----------------------+---------+-------------+

至此该条语句的优化完成。