Full text searching in innodb using a myisam slave/ghost table

I need to do full text indexing for two different colums in two different tables.

The only problem is that I'm using innodb and there is no way for me to do full text indexing.

I know that there are options such as sphinx and I took a look at the options, but I feel like it's a overkill because the colums to be full-text indexed are varchar(20).

A开发者_如何学Pythonnd, probably, there will be only around 1,000,000 rows "at most"

One simple option I'm thinkin is to duplicate the two tables only with the primary key and the varchar to be indexed using myisam.

any suggestion? is there any way to achieve this easily?

---

Your approach seems correct.

• create a new table with only the fields of interest (the pkey(s) and the varchar(s) of interest), engine myisam.

• create a full text index as needed on this new table.

The main twist is to keep this new table in sync with the master table. The best means to do so is to use triggers on insert, update and delete of the master table.

An additional twist is to prevent end-users from tampering with the full text indexed table. This can be done with triggers too, but there's a better way: create a db user (e.g. tg_user), and grant him ownership of this table. Then revoke all except select to other users. And make sure that you use the definer clause when creating the above-mentioned triggers.

---

I decided to go with a simple solution:

First, insert the row to the innodb table (master). Then, right after it, insert the row to the myisam table (not trigger).

Run a cron ever hour or so to ensure referential integrity.

The query would be something like this:

SELECT * 
FROM master 
WHERE master.ID NOT IN 
    (SELECT slave.ID FROM slave WHERE master.ID=slave.ID);

SELECT * 
FROM slave 
WHERE slave.ID NOT IN 
    (SELECT master.ID FROM master WHERE slave.ID=master.ID);

If a slave row refers to a master row that does not exist, delete.

If a slave row does not exist for a master row, insert.

It's not the best solution, but simple.