On Wed, Apr 2, 2008 at 11:32 PM, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > Hi, > > On myisam storage system > > mysql> explain select ui.user_id, ucp.user_id,ucp.payment_date from > user_info ui, user_course_payment ucp where ui.user_id=ucp.user_id; > +----+-------------+-------+--------+-------------------------------+-----------------------+---------+-----------------+--------+-------------+ > > | id | select_type | table | type | possible_keys | key > | key_len | ref | rows | Extra | > +----+-------------+-------+--------+-------------------------------+-----------------------+---------+-----------------+--------+-------------+ > | 1 | SIMPLE | ucp | index | PRIMARY,user_course_pay_comp1 | > user_course_pay_comp1 | 30 | NULL | 256721 | Using index | > | 1 | SIMPLE | ui | eq_ref | PRIMARY | > PRIMARY | 10 | dip.ucp.user_id | 1 | Using index | > > +----+-------------+-------+--------+-------------------------------+-----------------------+---------+-----------------+--------+-------------+ > > 2 rows in set (0.00 sec) > > > On innodb storage system > > mysql> explain select ui.user_id, ucp.user_id,ucp.payment_date from > user_info ui, user_course_payment ucp where ui.user_id=ucp.user_id; > > +----+-------------+-------+--------+-------------------------------+-------------------------+---------+-----------------+--------+-------------+ > > | id | select_type | table | type | possible_keys | key > | key_len | ref | rows | Extra | > > +----+-------------+-------+--------+-------------------------------+-------------------------+---------+-----------------+--------+-------------+ > | 1 | SIMPLE | ucp | index | PRIMARY,user_course_pay_comp1 | > idx_user_course_payment | 9 | NULL | 256519 | Using index | > | 1 | SIMPLE | ui | eq_ref | PRIMARY | > PRIMARY | 10 | dip.ucp.user_id | 1 | Using index | > +----+-------------+-------+--------+-------------------------------+-------------------------+---------+-----------------+--------+-------------+ > > 2 rows in set (0.00 sec) > > I have executed ANALYZE TABLE COMMAND on both the system (innodb and myisam) > Yet there is a small difference. Highlighted in red color > > Is it the behavior of myisam or innodb or interal working of the storage > engines. > > Thanks, > Krishna
ANALYZE TABLE: http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html What stands out to me is that the used key is different between the two explains and that innodb index is not present in the possible key column. Take a look at the key portion of http://dev.mysql.com/doc/refman/5.0/en/using-explain.html . Try FORCE INDEX hinting the query and see what if it comes back with: The query would be something like: explain select ui.user_id, ucp.user_id, ucp.payment_date from user_info ui FORCE INDEX(user_course_pay_comp1), user_course_payment ucp where ui.user_id=ucp.user_id; I am not an innodb expert (or frankly even a user) but my guess is that innodb can process the query somewhat more efficiently using a secondary index noted in the second manual entry cited above. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
