Just waiting for any reply . On Thu, Apr 3, 2008 at 11:01 PM, Jay Pipes <[EMAIL PROTECTED]> wrote:
> Please actually read my reply before asking the same question. As I > stated, InnoDB outputs *estimated* row counts in EXPLAIN, whereas MyISAM > outputs *accurate* row counts. > > -jay > > Krishna Chandra Prajapati 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 > > > > > > > > > > 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) > > > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
