Create a table "alias", and change table type

Question

Hello, I have a table called tb which is innodb - has over 1m rows.

I want to do a fulltext search on the table, but I can't do that because its innodb.

So, How do I create an "alias" of tb called tb2, meaning (a duplicate of the table, that updates automatically when the data of tb updates, but change the table type of tb2 to myisam )?

So, that way I can do a fulltext search on tb2, find the ids and SELECT the ids from tb1

Answer 1

• create a table with the same structure but myisam engine
• use triggers for INSERT, UPDATE, DELETE to maintain the consistent state between the two tables

But be careful, because it will make the write operations slower.

Answer 2

I personally think that this is not really a good idea because it will slow down writes and with the amount of data you have, your fulltext searches will be slow. I would sincerely recommend that you look into a dedicated full text search engine like Sphinx

http://sphinxsearch.com/

Answer 3

First, you would create a table using the following syntax:

create table tb2 like tb;

This creates a table with the same index structure as tb. Then, alter the table to make it myisam:

alter table tb2 engine='myisam';

Then, copy over the existing information:

insert into tb2 select * from tb;

In order to maintain the info, you would then need to create three triggers on tb... after insert, after update, and after delete. Each event would insert / update / delete the corresponding row in tb2.