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
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapatikc@gmail.com

On Wed, Apr 2, 2008 at 5:06 AM, Krishna Chandra Prajapati <
prajapatikc@gmail.com> 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 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
wultsch@gmail.com
wultsch (aim)

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

On Wed, Apr 2, 2008 at 8:52 AM, Jay Pipes <jay@mysql.com> wrote:

> 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

Also, if he was testing one storage engine vs another he might have
dumped the table and reimported with the new engine. If he did not
ANALYZE TABLE for a myisam then EXPLAIN will give large row counts in
my experience.

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

Hi,
I have executed ANALYZE TABLE for myisam tables, but still myisam is showing
more scanning of rows as compared to innodb. What does ANALYZE TABLE command
exactly do for myisam storage engine.

Thanks
Krishna


On Wed, Apr 2, 2008 at 9:48 PM, Rob Wultsch <wultsch@gmail.com> wrote:

> On Wed, Apr 2, 2008 at 8:52 AM, Jay Pipes <jay@mysql.com> wrote:
>
> > 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
>
> Also, if he was testing one storage engine vs another he might have
> dumped the table and reimported with the new engine. If he did not
> ANALYZE TABLE for a myisam then EXPLAIN will give large row counts in
> my experience.
>
> --
> Rob Wultsch
> wultsch@gmail.com
> wultsch (aim)
>



--
Krishna Chandra Prajapati

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 <wultsch@gmail.com> wrote:

> On Wed, Apr 2, 2008 at 5:06 AM, Krishna Chandra Prajapati <
> prajapatikc@gmail.com> 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 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
> wultsch@gmail.com
> wultsch (aim)
>



--
Krishna Chandra Prajapati

On Wed, Apr 2, 2008 at 11:32 PM, Krishna Chandra Prajapati
<prajapatikc@gmail.com> 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/...yze-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/...g-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
wultsch@gmail.com
wultsch (aim)

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 <wultsch@gmail.com> wrote:
>
>> On Wed, Apr 2, 2008 at 5:06 AM, Krishna Chandra Prajapati <
>> prajapatikc@gmail.com> 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 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
>> wultsch@gmail.com
>> wultsch (aim)
>>
>
>
>

Just waiting for any reply .

On Thu, Apr 3, 2008 at 11:01 PM, Jay Pipes <jay@mysql.com> 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 <wultsch@gmail.com> wrote:
> >
> > On Wed, Apr 2, 2008 at 5:06 AM, Krishna Chandra Prajapati <
> > > prajapatikc@gmail.com> 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 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
> > > wultsch@gmail.com
> > > wultsch (aim)
> > >
> > >
> >
> >
> >
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=y...0608@gmail.com
>
>


--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

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_val s).


On Sun, Apr 6, 2008 at 8:49 PM, Moon's Father <yueliangdao0608@gmail.com> wrote:
> Just waiting for any reply .
>
>
>
> On Thu, Apr 3, 2008 at 11:01 PM, Jay Pipes <jay@mysql.com> 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 <wultsch@gmail.com> wrote:
> > >
> > > On Wed, Apr 2, 2008 at 5:06 AM, Krishna Chandra Prajapati <
> > > > prajapatikc@gmail.com> 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 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
> > > > wultsch@gmail.com
> > > > wultsch (aim)
> > > >
> > > >
> > >
> > >
> > >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=y...0608@gmail.com
> >
> >
>
>
> --
> I'm a mysql DBA in china.
> More about me just visit here:
> http://yueliangdao0608.cublog.cn
>



--
high performance mysql consulting.
http://provenscaling.com

On Sun, Apr 6, 2008 at 9:57 PM, Eric Bergen <eric@provenscaling.com> wrote:
> 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_val s).

Thank you! I spent a bit trying to find details about how row counts
were computed, but with no luck (I probably did not know how to know
how to look...). Could the alternative I had proposed be accurate at
all?

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

Thanks a lot

On Mon, Apr 7, 2008 at 10:59 AM, Rob Wultsch <wultsch@gmail.com> wrote:

> On Sun, Apr 6, 2008 at 9:57 PM, Eric Bergen <eric@provenscaling.com>
> wrote:
> > 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_val s).
>
> Thank you! I spent a bit trying to find details about how row counts
> were computed, but with no luck (I probably did not know how to know
> how to look...). Could the alternative I had proposed be accurate at
> all?
>
> --
> Rob Wultsch
> wultsch@gmail.com
> wultsch (aim)
>



--
Krishna Chandra Prajapati