Hi - I want to write a web based app with a database backend for my wife who works in the recruitment industry. I was wondering whether I should use InnoDB or MyISAM for my tables.
MyISAM seems to have the ability to do a full text search while innodb does not. Innodb has the ability to maintain referential integrity while MyISAM does not.
Which of these is more important? I was thinking that i could maintain referential integrity in myisam by checking that foreign key values being inserted really do exist via my php code. However, there seems to be no workaround for the lack of full text search in InnoDB.
What are you thoughts? Please note that I really am new to databases - this will be my first ever database project. I'll post my database design seperately for feedback
well the full text search was going to be carried out on some free form fields where she can write notes for each discussion she has with a client. I was thinking maybe i can make the whole database in innodb and then just make the freeform text stuff in a sepearte table which is myisam..
I was thinking maybe i can make the whole database in innodb and then just make the freeform text stuff in a sepearte table which is myisam..
In my opinion, that is the ideal approach. You get the integrity offered by InnoDB where you really need it, and you get the "flat file" features offered by MyISAM where you need them... Using the best features of the different storage engines to get the solution that best suits your needs seems to be as good as you can get to me!
When you might consider using MyISAM after all and would like to implement referential integrity, don't do that in the php code of the webpages. Write some triggers that do that for you, otherwise you might end up writing that same php code several times. But I agree that the InnoDB with one MyISAM for fulltext search is the way to go.
> SELECT * FROM users WHERE clue > 0;
Empty set (0.00 sec)