The MyISAM isn't scanning more rows. It's that the InnoDB "rows" output
in EXPLAIN is an estimate and the MyISAM one is accurate...
-jay
Krishna Chandra Prajapati wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
