I am getting deadlocks on this table catalogsearch_query. The table has approx 500k rows.
The site is averaging about 2000 concurrent users.
Edit: To clarify concurrent users I mean the following:
Go to Google Analytics. Click on Real Time, Click on Overview view number in "Right Now"
The table is 129mb. (catalogsearch_query 129.34)
New Relic is giving me a really slow response time on this query
SQL: INSERT INTO `catalogsearch_result` SELECT ? AS `query_id`, `s`.`product_id`, MATCH (s.data_index) AGAINST (:query IN BOOLEAN MODE) AS `relevance` FROM `catalogsearch_fulltext` AS `s` INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = ?) AND (MATCH (s.data_index) AGAINST (:query IN BOOLEAN MODE) OR (`s`.`data_index` LIKE ? OR `s`.`data_index` LIKE ? OR `s`.`data_index` LIKE ?)) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`)
They are averaging 3500 orders per day with a peak of 8000 orders (42000 items) on Friday Nov 28th. I am not sure if that is busy or not but they are expecting a bigger day on Mon Dec 1.
First Question: It looks like the deadlocks are happening on the inserts. What are the ramifications if I reduce the size of this table or simply truncate it?
Second Question: If I can't truncate or remove data what are my other options?