I don't see what the issue is. As Jay said the row counts in explain outputs are estimates. When running an explain query MySQL asks the storage engine how many rows it thinks are between a set of values for an index. Different storage engines use different methods to calculate row count. Both innodb and myisam estimate the row count based on statistics they keep on the distribution of keys in an index. MyISAM is more accurate than innodb with it's row count because of how it keeps statistics. Analyze table on a myisam table will count the number of unique values in an index (myisam/mi_check:update_key_parts). Innodb samples the key distribution in 8 different pages per index and does some calculations based on the tree structure of those pages (details innobase/btr/btr0cur.c:btr_estimate_number_of_different_key_vals).
On Sun, Apr 6, 2008 at 8:49 PM, Moon's Father <[EMAIL PROTECTED]> wrote: > 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 > -- high performance mysql consulting. http://provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
