I have two tables called "Products" and "ProductSearch", each with 1 million rows and 1GB in size. "Products" table is the main InnoDB formatted table designed for displaying products throughout the site and allowing users to add/edit new products. While "ProductSearch" is a MyISAM table designed solely for search (because I need the FullText ability when customers search for items). Basically what I've been doing is copying items from "Products" and inserting them into "ProductSearch" if the id doesn't exist, using this query during a hourly cron job:
mysql_query("INSERT INTO productsearch SELECT * FROM products WHERE products.id NOT IN (SELECT productsearch.id FROM productsearch WHERE products.id=productsearch.id)");
The problem now is this is beginning to take a very long time because the tables are so large, and timeout errors are beginning to happen.
Is there a better and faster way of copying the tables and keeping them in sync?