Re: Innodb vs myisam
Thanks a lot On Mon, Apr 7, 2008 at 10:59 AM, Rob Wultsch [EMAIL PROTECTED] wrote: On Sun, Apr 6, 2008 at 9:57 PM, Eric Bergen [EMAIL PROTECTED] 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_vals). 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 [EMAIL PROTECTED] wultsch (aim) -- Krishna Chandra Prajapati
Re: Innodb vs myisam
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
Re: Innodb vs myisam
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
Re: Innodb vs myisam
On Sun, Apr 6, 2008 at 9:57 PM, Eric Bergen [EMAIL PROTECTED] 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_vals). 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 [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
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 [EMAIL PROTECTED] wrote: On Wed, Apr 2, 2008 at 8:52 AM, Jay Pipes [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wultsch (aim) -- Krishna Chandra Prajapati
Re: Innodb vs myisam
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
Re: Innodb vs myisam
On Wed, Apr 2, 2008 at 11:32 PM, Krishna Chandra Prajapati [EMAIL PROTECTED] 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/en/analyze-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/en/using-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 [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
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]
Re: Innodb vs myisam
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)
Re: Innodb vs myisam
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|
Re: Innodb vs myisam
On Wed, Apr 2, 2008 at 8:52 AM, Jay Pipes [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB vs MyISAM
On 2007-01-04 Octavian Rasnita wrote: I have seen that by default some tables are created as InnoDB and some as MyISAM. I guess the table type is not chosen randomly. How is it chosen the table engine used? You can set a global and IIRC a database specific default for the database type. And is InnoDB recommended now? It depends.. :) Does it support full text indexes? Or if not, is there a way of using full text indexes and foreign keys in MySQL? No fulltext indexes in InnoDB and no foreign keys in MyISAM as of 5.0. bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB vs MyISAM
InnoDB supports foreign keys, MyISAM does not. MyISAM supports full text indices, InnoDB does not. This is unfortunate. It has kept me using MyISAM where I'd rather use InnoDB, although fortunately none of my applications are really hampered by it. The only work-around I can think of is to create a separate MyISAM table that contains all of the fields you need for a full text index, and that links back to the InnoDB table. This is very wasteful, but at least the main table will have foreign keys and transaction recovery. The index table would be easy to recreate if it were lost. In fact, you might want to rebuild it periodically rather than maintain it in real time. This would make your application run faster when doing updates and inserts, if you can tolerate an index that isn't up-to-the-minute. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:38 AM To: mysql@lists.mysql.com Subject: InnoDB vs MyISAM Hi, I have seen that by default some tables are created as InnoDB and some as MyISAM. I guess the table type is not chosen randomly. How is it chosen the table engine used? And is InnoDB recommended now? Does it support full text indexes? Or if not, is there a way of using full text indexes and foreign keys in MySQL? Thank you very much. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB vs MyISAM
And is InnoDB recommended now? It depends.. :) Depends on... what? I mean, if I don't need transactions, is there another reason for using InnoDB? If it is necessary I can build the client program without foreign keys support also. Thanks. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB vs MyISAM
Octavian, 1) You can use MyISAM for example when you use static information in a webpage. For example, only for store information of customers, something like that.. 2) Innodb is a engine that support ACID, you can use for transactions. For example, load information of sales from PDA ( field) of a lot of sales man ( 1000). please see below http://en.wikipedia.org/wiki/ACID Bye, Juan On 1/4/07, Octavian Rasnita [EMAIL PROTECTED] wrote: And is InnoDB recommended now? It depends.. :) Depends on... what? I mean, if I don't need transactions, is there another reason for using InnoDB? If it is necessary I can build the client program without foreign keys support also. Thanks. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB vs MyISAM
At 08:38 AM 1/4/2007, you wrote: Hi, I have seen that by default some tables are created as InnoDB and some as MyISAM. I guess the table type is not chosen randomly. How is it chosen the table engine used? And is InnoDB recommended now? If you need transactions or RI. Does it support full text indexes? Or if not, is there a way of using full text indexes and foreign keys in MySQL? Innodb does not support full text indexes and MyISAM does not support foreign keys. InnoDb has been working on full text search for over a year (or two?) and it should be completed about a week before the sun turns into a red giant (some 3 billion years from now). If you can't wait that long you can use Sphinx and recompile MySQL to use that. It is faster and more efficient than MySQL's full text search and will work with InnoDb. Get it from http://www.sphinxsearch.com/. It is open source. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb vs myisam
Hi. Thanks for your response [EMAIL PROTECTED] wrote: Do you have any idexes on the table? No. The table looks like this. Would there be any advantage in creating indexes for it? | id | | LastUpdated| | 32957c615b37b5674f99d1cfd06d6a23 | | 20060416075614 | | 33d1d3a8b63c983a67ec5ab38d148774 | | 20060416003803 | | 3b0d2f1e5c2fe60377220aa146abd926 | | 20060411095312 | | 3c61d917967fb3ed45fa2ed8efb67aa8 | | 20060413150746 | | 4e0debc18ce34d8f131e05664f9df6da | | 20060411104352 | What does your mysql show create table tbl_name \G /~ mysql show create table sessions \G *** 1. row *** Table: sessions Create Table: CREATE TABLE `sessions` ( `id` char(32) NOT NULL, `a_session` longtext, `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql \_ Thanks. Kind regards. In theory, theory and practice are the same; in practice they are not. On Thu, 13 Apr 2006, Luke Vanderfluit wrote: To: mysql@lists.mysql.com From: Luke Vanderfluit [EMAIL PROTECTED] Subject: innodb vs myisam Hi. I have the following myisam table: The table is only 32,000 rows, but over 60Megs in size. And mysql seems to be wanting to write to that file alot, so it may well be trying to seek all over the disk looking for the right spot all the time. Does innodb do a better job at keeping the file on the disk smaller? Does an innodb table take up less disk space than myisam? Is an innodb table compacter and would therefore require less disk seek time or I/O than myisam? Kind regards. Luke. -- Luke
Re: innodb vs myisam
Do you have any idexes on the table? What does your mysql show create table tbl_name \G statement look like please? Keith In theory, theory and practice are the same; in practice they are not. On Thu, 13 Apr 2006, Luke Vanderfluit wrote: To: mysql@lists.mysql.com From: Luke Vanderfluit [EMAIL PROTECTED] Subject: innodb vs myisam Hi. I have the following myisam table: The table is only 32,000 rows, but over 60Megs in size. And mysql seems to be wanting to write to that file alot, so it may well be trying to seek all over the disk looking for the right spot all the time. Does innodb do a better job at keeping the file on the disk smaller? Does an innodb table take up less disk space than myisam? Is an innodb table compacter and would therefore require less disk seek time or I/O than myisam? Kind regards. Luke. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
The answer is actually quite simple! There are a few reasons: 1. Features. Each table type has something over the other. While InnoDB has transactions, foreign keys, hot backup capabilities, consistant read and better write concurrency (for many situations), MyISAM has FULLTEXT indexes, the option of having secondary AUTO_INCREMENT columns, OpenGIS data storage (in 4.1 and above) as well as slighly simplified offline backups. Additionally, MyISAM has lower disk space requirements for any given amount of data. 2. Price When looking at commercial licensing, it costs more to buy a version that includes InnoDB. Hope this helps! Regards, Chris On Fri, 24 Oct 2003 01:50 am, Travis Reeder wrote: I'm sure this has been asked before, but after seeing some benchmarks, it looks like using innodb is a no brainer. Just want to know why you wouldn't use innodb? Travis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
In the last episode (Oct 24), Chris Nolan said: The answer is actually quite simple! There are a few reasons: 1. Features. Each table type has something over the other. While InnoDB has transactions, foreign keys, hot backup capabilities, consistant read and better write concurrency (for many situations), MyISAM has FULLTEXT indexes, the option of having secondary AUTO_INCREMENT columns, OpenGIS data storage (in 4.1 and above) as well as slighly simplified offline backups. Additionally, MyISAM has lower disk space requirements for any given amount of data. MyISAM also lets you put indexes and tables onto separate disks for more performance, and supports a compressed read-only format. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
I thought I read a message on this list that said you can't use full text indexes with InnoDB yet. Can anyone confirm that? - Gabriel On Thursday, October 23, 2003, at 11:50 AM, Travis Reeder wrote: I'm sure this has been asked before, but after seeing some benchmarks, it looks like using innodb is a no brainer. Just want to know why you wouldn't use innodb? Travis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
At 02:37 PM 10/23/2003, you wrote: I thought I read a message on this list that said you can't use full text indexes with InnoDB yet. Can anyone confirm that? - Gabriel On Thursday, October 23, 2003, at 11:50 AM, Travis Reeder wrote: I'm sure this has been asked before, but after seeing some benchmarks, it looks like using innodb is a no brainer. Just want to know why you wouldn't use innodb? Travis Travis, Correct. Innodb DOES NOT support full text indexes. Someday perhaps, but not now. :-( Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB vs MyISAM
On Thu, Oct 09, 2003 at 01:23:39PM -0400, Don Vu wrote: Hi guys, Do both MyISAM tables and INNODB tables support foreign keys in 4.0.15? No. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 25 days, processed 959,424,306 queries (431/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB vs MyISAM
At 12:23 PM 10/9/2003, you wrote: Hi guys, Do both MyISAM tables and INNODB tables support foreign keys in 4.0.15? If so, are the main advantages of using INNODB tables the added features of transactions, cascading deletes, and it's other more robust features? Any thoughts on any disadvantages of INNODB to MyISAM? I know that INSERTS, UPDATES and DELETES will be slower with INNODB cuz of the transaction overhead, but do you think the SELECT speed is about the same? any feedback appreciated. thanks, Don Don, For my tables the select speed with InnoDb is about 10x slower than MyISAM. I didn't do any tuning but my feeling is if the table is readonly, stick with MyISAM. If you have a lot of updates to the table, then InnoDb is recommended. You can of course this with your own table structure to see how well it performs. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB vs myISAM disk space usage
Just to contribute our anecdotal experience, we also found a 2x increase in space required when we converted our MyISAM tables over to InnoDB. While it was surprising, it wasn't unexpected. We just had to go buy another 60GB of disk space (luckily we had planned for this). :) Owen On Wed, 2002-11-27 at 07:26, [EMAIL PROTECTED] wrote: Hello all, I'm using mySQL for many databases, now I want to test innoDB so I have created another DB (on the same machine) and I have populated it with the same data of the first DB (via: insert into table_name select * from db1.table_name); I have this tables: tableA: 80.000 recs tableB, tableC, tableD and tableE: 150.000 records (each) tableF: 275.000 recs tableG: 1.800.000 recs the problem is: in the myISAM tables disk usage of the DB is 376MB, with innoDN the datafile grows to 724MB (and show table status tells there is 37MB free) Anyone knows the reason of this disk usage of innoDB (it's 2 time bigger than myISAM) Tommaso - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB vs myISAM disk space usage
On Wed, Nov 27, 2002 at 01:26:42PM +0100, [EMAIL PROTECTED] wrote: Anyone knows the reason of this disk usage of innoDB (it's 2 time bigger than myISAM) InnoDB has larger per-record overhead (row headers and such). -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 7 days, processed 251,392,469 queries (382/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How stable is v4? (was: Re: InnoDB vs. MyISAM on large numberof tables?)
On Fri, 9 Aug 2002, Thomas Seifert wrote:
On Thu, 8 Aug 2002 03:02:40 -0700
Jeremy Zawodny [EMAIL PROTECTED] wrote:
btw:
I did a quick benchmark with mysql4 and its query caching running with
innodb.
Quite impressive, the app run with double the number of pages per second as
before.
Excellent. MySQL 4.0.{2,3} is working well for us too.
Is there 4.0.3 already somewhere to download?
Hi Thomas,
pulling from the source tree will give you a preliminary form of
4.0.3-beta. The fine manual tells how to do this.
At all, how stable is 4.0.x already? afaik its labelled alpha on the
website but I heard some talking about beta-quality.
I have it running on production, but it does a small set set of relatively
simple fulltext queries at a low load.
I would like to give it a shot on a production-system 'cause of that
fantastic speed increase.
Go ahead, if you can tolerate a very small risk. It's likely to work
fine.
Thomas Spahni
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How stable is v4? (was: Re: InnoDB vs. MyISAM on large number of tables?)
On Thu, 8 Aug 2002 03:02:40 -0700
Jeremy Zawodny [EMAIL PROTECTED] wrote:
btw:
I did a quick benchmark with mysql4 and its query caching running with
innodb.
Quite impressive, the app run with double the number of pages per second as
before.
Excellent. MySQL 4.0.{2,3} is working well for us too.
Is there 4.0.3 already somewhere to download?
At all, how stable is 4.0.x already? afaik its labelled alpha on the website but I
heard
some talking about beta-quality.
Is there an official bug-list for v4?
I would like to give it a shot on a production-system 'cause of that fantastic speed
increase.
Thomas
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How stable is v4? (was: Re: InnoDB vs. MyISAM on large number of tables?)
On Fri, Aug 09, 2002 at 09:21:13PM +0200, Thomas Seifert wrote:
On Thu, 8 Aug 2002 03:02:40 -0700
Jeremy Zawodny [EMAIL PROTECTED] wrote:
Excellent. MySQL 4.0.{2,3} is working well for us too.
Is there 4.0.3 already somewhere to download?
Not yet. I believe there will be a 4.0.3 beta release soon, though.
I've been building my own from the source tree.
At all, how stable is 4.0.x already? afaik its labelled alpha on the website but I
heard
some talking about beta-quality.
Is there an official bug-list for v4?
I would like to give it a shot on a production-system 'cause of that fantastic speed
increase.
Well, I'm running 4 production servers with various builds of 4.0.2 and
pre-4.0.3 (they're all slaves). Our master is 3.23.51. I hope to
upgrade it to 4.0.3 after the release (and I get a chance to prove it on
the slaves).
I'm not using all the fancy new 4.x features. Replication and InnoDB,
yes. No UNIONs, full-text search, or query cache.
My advice is to try it out if you can. Test carefully and keep and eye
on it for a few days. But I've had very good luck so far.
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.com/
MySQL 3.23.51: up 3 days, processed 79,775,387 queries (264/sec. avg)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb vs myisam optimizations
Hi! On Dec 04, Gurupartap Davis wrote: I'm converting a table to innodb from myisam in mysql 4.0 and I was wondering why it takes sooo long to do a SELECT COUNT(*) MyISAM stores total number of rows in MYI file header. It's read into memory when table is opened. So for SELECT COUNT(*) FROM MyISAM_table NySQL need not acess data or even index - it can return an answer immidiately. InnoDB doesn't store total number of rows anywhere (afaik). Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB vs MyISAM on COUNT(*) ... WHERE ...
I believe the MyISAM format is so fast on simple SELECT count(*) FROM table; because it actually keeps the current number of rows as a table statistic - so it doesnt have to look at the data at all. Using a WHERE clause, however, will force MyISAM to actually select all the rows and count how many it finds. This is what InnoDB tables and others do. Of course doing your own testing shouldnt be that hard and is always the best way to figure out how your application will hold up. dpk - Original Message - From: Sander Pilon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 12:19 PM Subject: InnoDB vs MyISAM on COUNT(*) ... WHERE ... I'm thinking of switching to InnoDB, however - my application does a few COUNT(*) WHERE queries on large tables (somewhere between 50K and 2M rows) I've read up on InnoDB and its issues with COUNT(*) on entire tables, but is there a reason to assume that InnoDB is also slower when there is a WHERE clause present? Regards, Sander - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
