Create a table "alias", and change table type

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 c开发者_JAVA技巧hange 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

---

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/

---

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

---

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.