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
You can create a mirror table. That's probably less than ideal, as the MyISAM table will not respect your transactions (if a transaction failed on InnoDB, your changes made to MyISAM in that transaction will still appear).

You could use a dedicated full-text search system like [Sphinx](http://www.sphinxsearch.com/), which is what I have used for full-text searching (Since my database is InnoDB).