Re: 5.0 and 5.5 performance question--select

[ning wang]

on 5.0 --use time:15 rows in set (0.17 sec) and explain

+----+--------------------+-----------------------+--------+----------------------------+-------------+---------+---------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------+--------+----------------------------+-------------+---------+---------------------------------+------+---------------------------------+
| 1 | PRIMARY | crm_contract_template | ALL | PRIMARY | NULL | NULL | NULL | 645 | Using temporary; Using filesort |
| 1 | PRIMARY | crm_contract | ref | template_id,IN_template_id | template_id | 5 | td_oa.crm_contract_template.id | 13 | Using where |
| 1 | PRIMARY | crm_country | eq_ref | PRIMARY | PRIMARY | 4 | td_oa.crm_contract.prim_country | 1 | Using where; Using index |
| 1 | PRIMARY | FLOW_RUN_PRCS | ref | RUN_ID,PRCS_FLAG | RUN_ID | 4 | td_oa.crm_contract.RUN_ID | 2 | |
| 1 | PRIMARY | FLOW_PROCESS | ref | FLOW_ID,FORM_ID | FORM_ID | 4 | td_oa.FLOW_RUN_PRCS.FLOW_PRCS | 2 | |
| 2 | DEPENDENT SUBQUERY | X | ref | RUN_ID | RUN_ID | 4 | td_oa.FLOW_RUN_PRCS.RUN_ID | 2 | Using where; Using filesort |
+----+--------------------+-----------------------+--------+----------------------------+-------------+---------+---------------------------------+------+---------------------------------+



on 5.5 ---use time:15 rows in set (9.54 sec) and explain
+----+--------------------+-----------------------+--------+----------------------------+---------+---------+---------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------+--------+----------------------------+---------+---------+---------------------------------+------+--------------------------+
| 1 | PRIMARY | crm_contract | index | template_id,IN_template_id | PRIMARY | 4 | NULL | 12 | Using where |
| 1 | PRIMARY | crm_contract_template | eq_ref | PRIMARY | PRIMARY | 4 | td_oa.crm_contract.template_id | 1 | |
| 1 | PRIMARY | crm_country | eq_ref | PRIMARY | PRIMARY | 4 | td_oa.crm_contract.prim_country | 1 | Using where; Using index |
| 1 | PRIMARY | FLOW_RUN_PRCS | ref | RUN_ID,PRCS_FLAG | RUN_ID | 4 | td_oa.crm_contract.RUN_ID | 2 | |
| 1 | PRIMARY | FLOW_PROCESS | ref | FLOW_ID,FORM_ID | FORM_ID | 4 | td_oa.FLOW_RUN_PRCS.FLOW_PRCS | 2 | |
| 2 | DEPENDENT SUBQUERY | X | index | RUN_ID | PRCS_ID | 4 | NULL | 1 | Using where |
+----+--------------------+-----------------------+--------+----------------------------+---------+---------+---------------------------------+------+--------------------------+