Question: I'm designing an internal web application that uses MySQL as its backend database. The integrity of the data is crucial, so I am using the `innoDB` engine for its foreign key constraint features.

I want to do a full-text search of one type of records, and that is not supported natively with innoDB tables. I'm not willing to move to `MyISAM` tables due to their lack of foreign key support and due to the fact that their locking is per table, not per row.

Would it be bad practice to create a mirrored table of the records I need to search using the MyISAM engine and use that for the full-text search? This way I'm just searching a copy of the data and if anything happens to that data it's not as big of a deal because it can always be re-created.

Or is this an awkward way of doing this that should be avoided?

Thanks.

Answer: 
I think its truly awkward. That said, my "quick prototype that will probably accidentally become production code" method of doing this is something like this:

```
CREATE TEMPORARY TABLE search_mirror (FULLTEXT INDEX (col1, col2, ...)) Engine=MyISAM SELECT * FROM original_innodb_table;

SELECT * FROM search_mirror WHERE MATCH(col1, col2, ...) AGAINST ('foo');

DROP TEMPORARY TABLE search_mirror;

```

And for bonus points you could do all of that inside a transaction should that suit your fancy (double bonus if you're using non-persistent connections and only searching once per connect, as you can then eliminate the drop statement).

Yes I realize that this is not true mirroring/replication. Yes I realize duping the table can be expensive (relatively small datasets here). Like I said, quick and dirty prototype. YMMV
I feel that the simplest solution for this problem is the creation of a index table which will be used for searches, with a pointer back to the table which contains the real data. I'm facing exactly the same problem and I do not want to use MyISAM tables for my system because of the peace of mind given by InnoDB tables.

So, what I'm planning to do with my problem is to create an index table using MyISAM, so I can have only the information to be indexed on it. Synchronization will be done using triggers, which is the most simplest way to do so. I do not want to replicate the entire table, since it will cost a lot of space. However, replicating only the desired fields will cost space at the expense of the search engine facility.

This index table can be understood as an index for searching facilities. As any index, it will cost space. As an optimization, the inserted data on this index table can be only terms, but this way extra processing is needed in order to clean useless word for searching.