MySQL Full-text Search Workaround for innoDB tables

mysql search innodb myisam full-text-search
Rob picture Rob · Apr 14, 2010 · Viewed 15k times · Source

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

michael picture michael · Jun 1, 2010

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.

Related questions

Fulltext Search with InnoDB

I'm developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly. I was originally planning on using MyISAM for the tables (for the built-in fulltext …

Read More
MySQL - How to ORDER BY RELEVANCE? INNODB Table

I've got about 20,000 rows in an INNODB table called 'cards', so FULLTEXT is not an option. Please consider this table: id | name | description ---------------------------------------------------------- 1 John Smith Just some dude 2 Ted Johnson Another dude 3 Johnathan Todd This guy too 4 Susan Smith …

Read More
Search for string within text column in MySQL

I have mysql table that has a column that stores xml as a string. I need to find all tuples where the xml column contains a given string of 6 characters. Nothing else matters--all I need to know is if this 6 …

Read More