I am designing a new db for my website.
It is a database of events with the following tables.

I am left with the following dilemma,
I use foreign keys to ensure fast links between the table Events and other tables (category, countries,...)
this means using InnoDB
Now I want to have the abillity to use fulltext search on the table Events but that would mean using Myisam.

How do I fix this?
Also, using Myisam would eventually result in waiting periods as it would use lock table instead of lock row.

anny suggestions?

6 Years
Discussion Span
Last Post by smantscheff

First make sure that the MySQL full text search really fits your needs. More often than not it doesn't.
If it does, work with triggers. Have your referential keys in InnoDB tables and the text data in MyISAM and have the latter updated by operations on the former (or vice versa).


tnx for the reply, but I don't quite follow.
What do you mean , by full text search fits your needs?
Do you mean I might better use something like "where title like '%searchstring%'" then a fulltext search? I don't know wich is better:
on the page http://www.tornooi.net/events?e=570 you see an avarage entry my db
everyting between "LUSV basketball toernooi van 25/02 tot 27/02/2011" and "accomodatie" is the description.
I thought I needed fulltext search (but it is the fist time I use it...)

also, could you give a simple example of working wit triggers and diffrent tables?


I wouldn't like to deal with the quirks of the MySQL fulltext engine. For example, when I last used it it had a minimum search word length of 3 or 4 characters, so you could not search for "db". I don't know if they changed that, but I was always better off doing my own search routines.
For triggers, consult the manual: http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html
There are lots of examples there.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.