MyISAM vs innoDB (continued)

[dr_lucas]

Almost 5 years ago I started the "MyISAM vs innoDB" discussion thread, which unfortunately has been archived, thus the reason for this new thread.

Original thread: http://www.oscommerce.com/forums/topic/342510-myisam-vs-innodb/

 

In his response, Matt (@@ecartz) wrote:

You should use MyISAM for product tables and other places where you need full text search, which InnoDB does not have. If you use InnoDB for the order tables, you should modify the queries to take advantage of transactions (primarily in the checkout_process.php file) and foreign key constraints. Without those modifications, InnoDB won't be more reliable than MyISAM.

 

Note that some tables will have trouble with foreign key constraints. Notably both address_book and customers have their primary keys as data in the other table. However, obviously only one could be a foreign key constraint (chicken and egg problem).

 

In general, I would expect MyISAM to be faster for reads. InnoDB may be faster for writes under some circumstances. However, most tables are read more than they are written. As such, it is usually better to optimize for the reading case rather than the writing case. In osCommerce, the order tables may be an example of an exception.

 

Since then, there has been obviously a lot of development and drastic improvements on innoDB in MySQL/MariaDB and InnoDB also currently has "full text search".

I have discussed MyISAM with many people, including some DBAs and almost all of them said more or less similar things that sum up to: "It's 2014, there is no reason to use MyISAM, avoid it at all costs".

 

I am really interested to know what are your thoughts on this subject, especially in regards to using InnoDB on osCommerce (2.2 RC2a in my case, but I am also talking about 2.3.x and up).

[ozEworks]

We use both quite successfully. For osCommerce we used MyISAM by default. But for custom products like qdPM Extended we use InnoDB. qdPM Extended has extensive search capability and we've not had any issues. I see no real argument to the avoid MyISAM at all costs. We have numerous osCommerce sites using it and some have very large databases.

[redrum]

I also want some input in this matter.

 

My mainly issue is that the tables 'sessions' and 'whos_online' sometimes crasch and give me the following error message:

145 - Table './xxx/sessions' is marked as crashed and should be repaired

select value from sessions where sesskey = '60fa1fab3a3d285cfb7bc1c93bb85f64' and expiry > '1395226673'

[TEP STOP]

 

I guess that if I change 'sessions' and 'whos_online' from MyISAM to InnoDB then these tables will be self healing if they crash. Am I correct in this matter?

 

Would it cause any problems by changing these tables from MyISAM to InnoDB?

[iRAY]

You can safely change both tables to InnoDB. As far as I know in osCommerce core there are no SQL queries which would start to fail then.