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]
