Magento Forum

Storage engine : myISAM or InnoDB
 
Capitaine Commerce
Member
 
Avatar
Total Posts:  68
Joined:  2007-09-01
Lille, France
 

Since I get strange behaviors on my Magento database, I’ve got a look at the storage engines of the table.
For example, catalog_product_entity’s storage engine is myISAM.
Is that correct ? Shoudn’t it be InnoDB. I’m quite doubtful…

 
Magento Community Magento Community
Magento Community
Magento Community
 
Mark_Kimsal
Sr. Member
 
Total Posts:  186
Joined:  2007-09-12
Michigan, USA
 

All my tables are InnoDB except for the ones that start with log_* and paygate_authorizenet_debug.  Those exceptions are MyISAM.  What version are you on?  I did a fresh install of 0.6.14100

 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

This is something that has confused me a bit. I see all my tables to be InnoDB tables in version 0.8.16100 ( latest as of date). Though the sample data file script that loads sample data drop existing tables and creates MYISAM tables ...seems odd ..wish someone could clarify why the sample data script is out of sync with the base installation of magento? Also which is the correct one?

My feeling is that it should be MYISAM as InnoDB is more used for transaction handling whereas as an e-commerce store, the scope for transaction handling is relevant only at the time of order booking. So all other tables need to be MYISAM which is more optimized for performance (particularly search).

 
Magento Community Magento Community
Magento Community
Magento Community
 
AdobeShinobi
Jr. Member
 
Total Posts:  3
Joined:  2008-02-04
 

But they use a lot of REFERENCES to other tables so I think that they’re suppose to be InnoDB since MySQL doesn’t support them.

 
Magento Community Magento Community
Magento Community
Magento Community
 
lorenzo01
Sr. Member
 
Avatar
Total Posts:  221
Joined:  2007-09-06
France
 

finally, innoDb or MyIsam ? What is the best solution ??
thanks

 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

hmmm ...as AdobeShinobi pointed, magento seems to be using a lot of Foreign Key references and MYSQL as of now does not seem to be supporting Foreign key references yet (refer
http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html)

But I do believe that MYISAM would be more approriate for an ecommerce system where the search functionality is more important and performance has top priority. Well atleast for the search related tables and on Attribute tables where MYISAM offers a significant performance boost through its index caching. A programmatic check in code may be more optimized by than enforcing referential integrity through foreign keys for the following tables atleast -
All catalog tables like catalog_product_entity, catalog_product_entity_varchar, catalog_product_entity_text, etc
All EAV attribute tables

http://dev.mysql.com/doc/refman/4.1/en/myisam-key-cache.html

In retrospect, I wonder why the INNODB implementation was preferred for these tables.

 
Magento Community Magento Community
Magento Community
Magento Community
 
aimos
Jr. Member
 
Total Posts:  18
Joined:  2007-12-28
 

innoDB is the advanced storage engine of mysql. It supports foreign key checks AND transactions.

If your whole db is build on innodb AND you want to add fulltext search you have to double some
tables and fill them with data with a link back to the source table. You don’t need to build all your db
on myIsam to achive that.

InnoDB is ok.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top