1

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.

Shaokan
  • 7,438
  • 15
  • 56
  • 80

2 Answers2

3

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).

Community
  • 1
  • 1
Jan-Henk
  • 4,864
  • 1
  • 24
  • 38
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.

Savas Vedova
  • 5,622
  • 2
  • 28
  • 44