Page 1 of 1

MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Posted: Thu Apr 23, 2009 12:00 pm
by JellyFish
How do I get both FULLTEXT indexes and foreign key constraint indexes for the same table? InnoDB supports foreign key constraints but not FULLTEXT indexes, while MyISAM supports FULLTEXT but not foreign key constraints. How do I get both into one table? Is there another engine type that supports both abilities? Or does MySQL not allow these two capabilities in one table?

Thanks for taking the time to read this post. All help is appreciated.

[EDIT] From my research it seems as though I can't have both full text search and foreign key constraints in the same table. So what am I to do?

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Posted: Thu Apr 23, 2009 1:07 pm
by Eran
Use sphinx for text search and InnoDB as the storage engine.

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Posted: Thu Apr 23, 2009 1:52 pm
by JellyFish
I'm on a shared server; is there any other solution, sense I can't install anything on my server?

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Posted: Thu Apr 23, 2009 1:56 pm
by Eran
Each storage engine has its own advantages and disadvantages. Decide what is more important to you and go with that. For small tables you can use the LIKE operator with acceptable performances.

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Posted: Thu Apr 23, 2009 2:51 pm
by pickle
There are more than just MyISAM and InnoDB table types. See if any of the other engines are available and will do what you want.

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Posted: Thu Apr 23, 2009 5:21 pm
by JellyFish
pickle wrote:There are more than just MyISAM and InnoDB table types. See if any of the other engines are available and will do what you want.
Not that I can find. I've look here and couldn't seem to find an engine that supports both full text search and foreign key constraints.

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Posted: Thu Apr 23, 2009 5:56 pm
by pickle
One that isn't listed there is Maria - the successor to MyISAM, developed by the guy who invented MySQL. At a quick glance I didn't see anything about foreign keys, however.

Re: MyISAM has FULLTEXT, but InnoDB has Foreign Key Constraints

Posted: Thu Apr 23, 2009 10:35 pm
by JellyFish
pickle wrote:One that isn't listed there is Maria - the successor to MyISAM, developed by the guy who invented MySQL. At a quick glance I didn't see anything about foreign keys, however.
You would think that if there was a database engine type that supported both foreign key constraints and full text search, that it would be a little more popular/well-known. I don't what engines my hosting provider provides, nor do I know how to find out (I might have to contact them). I've googled for full text and foreign keys in the same table but didn't find anything mentioning a specific engine type. Maybe I'll have to either give up foreign keys and code the restrictions or give up the full text search and create a keywords table of keyword indexes.