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.

Solution 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.

Solution 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

Solution 3

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, which is what I have used for full-text searching (Since my database is InnoDB).

Solution 4

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

Solution 5

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.

View more solutions