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 On Wed, Apr 2, 2008 at 9:06 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote: > On Wed, Apr 2, 2008 at 5:06 AM, Krishna Chandra Prajapati < > [EMAIL PROTECTED]> wrote: > > > Horribly ugly stuff.... > > > > I know I sure as heck am not going to spend half an hour to turn those > queries into something understandable, and I expect no one else will > either. If you want help please remove all extraneous details (turn table > and columns names in t1,t2,col1,col2, etc or descriptive names like parent, > child, datetime_end) and send out something that is easy to reproduce. You > get a cupcake if you include ddl that populates itself with random data. > > Also, using /G instead of a semi colon will make database output a heck of > a lot easier to read in email form. > > -- > Rob Wultsch > [EMAIL PROTECTED] > wultsch (aim) > -- Krishna Chandra Prajapati
