InnoDB vs MyISAM
In my particular situation, I have a small site with about 6,000 registered users. The FK InnoDB constraints are useful for enforcing data integrity, but I'm not sure what other benefits there might be ... Especially since I'm losing out on FULLTEXT searches, which can be very helpful to me.
Is it worth changing my tables to MyISAM in order to use FULLTEXT, or are there other benefits of InnoDB that mean I have to do this with my search solution LIKE %keyword%
?
(Note: The site is hosted on shared hosting, so I don't think third party search solutions like Sphinx are viable.)
source to share
Instead of links to wiki or other comparison pages, check them out (MyISAM and InnoDB) at dev.mysql.com
As far as MyISAM limits go, they are mostly disk / column size.
- Large files up to 63 bits are supported.
- There is a limit of 264 (1.8 * 1019) rows in MyISAM table.
- The maximum number of indexes in a MyISAM table is 64.
- The maximum number of columns per index is 16.
- The maximum key length is 1000 bytes.
InnoDB is pretty much newer and has a lot of support from the MySQL team, so using it for small and easily recoverable / recoverable tables is what I'm doing right now. Data that gets updated / inserted more often is still using MyISAM on my own server.
source to share
According to this link, InnoDB now supports full text search
http://blogs.innodb.com/wp/2011/07/innodb-full-text-search-tutorial/
(note) I'll also have to read this a little more, but it should be interesting nonetheless.
source to share