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
Good news! In MySQL 5.6 and up, full-text indexes can be used with InnoDB tables. You should consider updating your MySQL to 5.6 or up if you haven't as yet. 

With my application full-text search was very important so I just used MyISAM. Now, I have updated MySQL to 5.6, converted the database to InnoDB and added the correct constraints. Best of bother worlds.

[MySQL 5.6 Manual - Full-Text Search Functions](http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html)