MySQL Full-text Search Workaround for innoDB tables

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 1

You might be able to do some kind of data sync using triggers (if your version of mysql supports them). They allow you to run small snippets of SQL at certain points such as after data is inserted into or deleted from a table.

For example...

create trigger TRIGGER_NAME after insert on INNODB_TABLE
insert into MYISAM_TABLE select * from INNODB_TABLE
where id = last_insert_id();

... Whenever data is inserted into the INNODB table, the same data is automatically inserted into the MYISAM table.

Answer 2

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