MYSQL innodb or myisam? Which one is best for this case?

Question

I'm really lost on which type of database engine should I pick for my table.

+-----------------------+
| id | userid | content |
+-----------------------+

Imagine this table. userid is holding user ids which are stored in another table. Also, some other tables are using the id field of this table. Therefore, I thought that setting id as primary key and userid as a foreign key would speed up the join processes. However, if I select my table as InnoDB to set foreign keys, then I cannot conduct a FULLTEXT search on content (which is a TEXT field).

So basically, if I switch back to MyISAM to use the FULLTEXT searches, will I have problems when joining, say, 3-4 tables of hundreds of millions of rows?

PS: If there is another liable way to create tables to handle both joins and fulltexts, please tell me so, I can change the tables structure as well.

Answer 1

InnoDB scales better than MyISAM. If you're talking about hundreds of millions of row then go for InnoDB and adapt a search engine. AFAIK, FULLTEXT becomes really slow after a certain point. Therefore, go for InnoDB + a search engine of your choice.

Answer 2

Take a look at the answer for this question: Fulltext Search with InnoDB

In short, MyISAM locks an entire table when you write to it, so that will be bad for performance when you have a lot of writes to the table. The solution is to go for the InnoDB tables for the referential integrity, and use a dedicated search engine for the indexing/searchfing of the content (for example Lucene).