Hi All, I have same table configuration, every thing same except the storage engine.
Explain result on innodb system mysql> explain select ucpr.course_amount, ucpr.coupon_amount, ucp.payment_order_id, ui.course_id, uct.ref, uet.ref, ui.user_id, ucpr.coupon, ucp.payment_service_id, ucp.payment_id FROM user_course_pricing ucpr, user_info ui, course c, user_course_payment ucp left outer join user_cc_trans uct on ucp.payment_order_id=uct.payment_order_id left outer join user_ec_trans uet on ucp.payment_order_id=uet.payment_order_id WHERE ucp.payment_order_id is not null and date_format(ucp.payment_date, '%m-%Y')='05-2007' and ucp.user_id = ucpr.user_id and ucp.user_id = ui.user_id and ui.course_id = c.course_id and ucp.payment_id in (1, 2, 5, 6) and ui.course_id not in (1005, 1007, 1008) and ui.course_id not in (select course_id from course_attribute where ATTRIBUTE ='HOSTED_AFFILIATE') ORDER BY ucp.Payment_date; +----+--------------------+------------------+-----------------+-------------------------------+------------------------+---------+--------------------------+-------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------------+-----------------+-------------------------------+------------------------+---------+--------------------------+-------+-----------------------------------------------------------+ | 1 | PRIMARY | c | range | PRIMARY | PRIMARY | 10 | NULL | 134 | Using where; Using index; Using temporary; Using filesort | | 1 | PRIMARY | ui | ref | PRIMARY,idx_user_info_2 | idx_user_info_2 | 10 | dip.c.course_id | 279 | Using index | | 1 | PRIMARY | ucp | eq_ref | PRIMARY,user_course_pay_comp1 | PRIMARY | 10 | dip.ui.user_id | 1 | Using where | | 1 | PRIMARY | ucpr | eq_ref | PRIMARY | PRIMARY | 10 | dip.ucp.user_id | 1 | Using where | | 1 | PRIMARY | uct | ref | user_cc_trans_order_id | user_cc_trans_order_id | 10 | dip.ucp.payment_order_id | 1 | | | 1 | PRIMARY | uet | index | NULL | idx_user_ec_trans | 35 | NULL | 13959 | Using index | | 2 | DEPENDENT SUBQUERY | course_attribute | unique_subquery | PRIMARY | PRIMARY | 44 | func,const | 1 | Using index; Using where | +----+--------------------+------------------+-----------------+-------------------------------+------------------------+---------+--------------------------+-------+-----------------------------------------------------------+ 7 rows in set (0.00 sec) Explain result on myisam system mysql> explain -> select ucpr.course_amount, ucpr.coupon_amount, ucp.payment_order_id, ui.course_id, uct.ref, uet.ref, ui.user_id, ucpr.coupon, -> ucp.payment_service_id, ucp.payment_id -> FROM user_course_pricing ucpr, user_info ui, course c, user_course_payment ucp left outer join user_cc_trans uct on -> ucp.payment_order_id=uct.payment_order_id left outer join user_ec_trans uet on ucp.payment_order_id=uet.payment_order_id -> WHERE ucp.payment_order_id is not null and date_format(ucp.payment_date, '%m-%Y')='05-2007' and ucp.user_id = ucpr.user_id -> and ucp.user_id = ui.user_id and ui.course_id = c.course_id and ucp.payment_id in (1, 2, 5, 6) and ui.course_id not in -> (1005, 1007, 1008) and ui.course_id not in (select course_id from course_attribute where ATTRIBUTE ='HOSTED_AFFILIATE') -> ORDER BY ucp.Payment_date; +----+--------------------+------------------+-----------------+-------------------------------+------------------------+---------+--------------------------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------------+-----------------+-------------------------------+------------------------+---------+--------------------------+--------+----------------------------------------------+ | 1 | PRIMARY | ucp | ALL | PRIMARY,user_course_pay_comp1 | NULL | NULL | NULL | 256721 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | ucpr | eq_ref | PRIMARY | PRIMARY | 10 | dip.ucp.user_id | 1 | | | 1 | PRIMARY | ui | eq_ref | PRIMARY,idx_user_info_2 | PRIMARY | 10 | dip.ucp.user_id | 1 | Using where | | 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 10 | dip.ui.course_id | 1 | Using index | | 1 | PRIMARY | uct | ref | user_cc_trans_order_id | user_cc_trans_order_id | 10 | dip.ucp.payment_order_id | 1 | | | 1 | PRIMARY | uet | ALL | NULL | NULL | NULL | NULL | 13947 | | | 2 | DEPENDENT SUBQUERY | course_attribute | unique_subquery | PRIMARY | PRIMARY | 44 | func,const | 1 | Using index; Using where | +----+--------------------+------------------+-----------------+-------------------------------+------------------------+---------+--------------------------+--------+----------------------------------------------+ 7 rows in set (0.06 sec) I don't understand why the myisam system is scanning more number of rows as compared to innodb system -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 500003 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]