Searching a database with engine type INNODB

[cs3mw]

Ive been creating a news website using mysql and php and hit a snag now in regard to my database type. Throughout the project I have been defining my tables using a INNODB engine however now that I am trying to create a search facility i.e. a FULL TEXT SEARCH the mysql tech guide states that the database engine must be MYISAM. Should I change the engine or is there another way to search a particular varchar field using another method which works as well as FULL TEXT SEARCH i.e. LIKE? Any advice will be most appreciated.

Regards

Mike

[chazzy]

i guess it depends, what features of the InnoDB engine are you using? Are you aware that MySQL's going to stop shipping w/ InnoDB (once their replacement engine comes out...)

Edit: I've never tried it, but maybe you can run a boolean FTS on an InnoDB table, since it doesn't use a FTS key.
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

[cs3mw]

Yes I did hear a whisper on the grapevine in regard to mysql. However I done a little further redaing and some suggest having two different tables i.e. a innodb and a myisam which would be used for insert, update (innodb) and a myisam one for select statements? Has anyone done anything like this before? It sort of makes sense yet to have two tables which could grow to an extremely large amount seems a little strange!!

[chazzy]

it's typically thought of in an "online" and "offline" sense. The online (where you insert/update) always has the latest information, the offline (where you select) is always a few steps behind. Most implementations using this approach had more to do with large scale apps, and the entire schema was replicated into online/offline.

But answer my original question, what features of innodb are you using? If it's just for FK's, you could get similar result w/ triggers, just no index benefit. just a thought.

[cs3mw]

Sorry, yes Im just using them for FKs so il take your suggestions on board. For anyone else reading up on this type of problem have a look at this link

http://mysqldatabaseadministration.blogspot.com/2006/02/innodb-or-myisam-whats-your-preference.html