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?
this may be far too late to consider, but instead of two tables that are full blown copies of each other, you could have two tables that split the columns – non-searchable columns in the main innodb table, and searchable columns in the myisam table
the only difference is, when adding a new product, you need two inserts (similarly deletes)
no further syncing necessary
how many columns are indexed for fulltext? what proportion are these of all columns?
Both products.id and productsearch.id are Primary Unique Indexes for the table.
Thanks for the suggestion. I have a composite fulltext index that includes the “title” and “description” columns. Splitting things up like this could be too complex to implement at the moment, but this could be a good idea if I can figure something out on our site for a full overhaul.
Any other suggestions would be greatly appreciated.