Best practice: FULLTEXT search & InnoDB transactions & replication

[Patrick Savelberg]

Hi,

We have moved from Mysql4 to MySQL5 and are currently planning our new database schema. In this new approach we would like to move to InnoDB's storage engine for transaction support and still want to use MySQL's FULLTEXT search capabillities. And to make things easy we also want to replicate all our data to a second database.

Now I have two different possible approaches:

1. All tables are of type InnoDB, except one table which is of type MyIsam <= the FULLTEXT searchable table. This searchable table would have a column with searchable text and a few meta data columns to identify the originating table, column and row. I could use the triggers to index the desired columns on Inserts, updates and deletes and insert the indexed data into the MyIsam search-table.
Replication would be straigtforward 1-to-1 replication in this aproach.

2. Still all tables would be of type InnoDB, but instead of creating a single searchable MyIsam table I could also alter the storage engine type for the searchable tables on de replication slave to MyIsam and delegate all searches to the slave. Which even may improve performance, because the master wont be doing full text searches anymore.
Replication would be a bit more tricky because of having the InnoDB tables in the master and their corresponding MyIsam tables in the slave.

I'm wondering which, if any, of the above aproaches is advisable or if there are other aproaches which are even better.