MySQL query paralyzes site

Question

Once in a while, at random intervals, our website gets completely paralyzed.

Looking at SHOW FULL PROCESSLIST;, I've noticed that when this happens, there is a specific query that is "Copying to tmp table" for a loooong time (sometimes 350 seconds), and almost all the other queries are "Locked".

The part I don't understand is that 90% of the time, this query runs fine. I see it going through in the process list and it finishes pretty quickly most of the time. This query is being called by an ajax call on our homepage to display product recommendations based your browsing history (a la amazon).

Just sometimes, randomly (but too often), it gets stuck at "copying to tmp table".

Here is a caught instance of the query that was up 109 seconds when I looked:

SELECT DISTINCT product_product.id, product_product.name, product_product.retailprice, product_product.imageurl, product_product.thumbnailurl,   product_product.msrp
FROM product_product, product_xref, product_viewhistory
WHERE
(
(product_viewhistory.productId = product_xref.product_id_1 AND product_xref.product_id_2 = product_product.id)
OR
(product_viewhistory.productId = product_xref.product_id_2 AND product_xref.product_id_1 = product_product.id)
)
AND product_product.outofstock='N'
AND product_viewhistory.cookieId = '188af1efad392c2adf82'
AND product_viewhistory.productId IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172)
ORDER BY product_xref.hits DESC
LIMIT 10

Of course the "cookieId" and the list of "productId" changes dynamically depending on the request.

I use php with PDO.

Edit: I figured some of the table structures involved might help:

CREATE TABLE IF NOT EXISTS `product_viewhistory` (
  `userId` int(10) unsigned NOT NULL default '0',
  `cookieId` varchar(30) collate utf8_unicode_ci NOT NULL,
  `productId` int(11) NOT NULL,
  `viewTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
  KEY `userId` (`userId`),
  KEY `cookieId` (`cookieId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_xref` (
  `id` int(11) NOT NULL auto_increment,
  `product_id_1` int(11) default NULL,
  `product_id_2` int(11) default NULL,
  `hits` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `IDX_PROD1` (`product_id_1`),
  KEY `IDX_PROD2` (`product_id_2`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=184531 ;

CREATE TABLE IF NOT EXISTS `product_product` (
  `id` int(11) NOT NULL auto_increment,
  `supplierid` int(11) NOT NULL default '0',
  `suppliersku` varchar(100) NOT NULL default '',
  `name` varchar(100) NOT NULL default '',
  `cost` decimal(10,2) NOT NULL default '0.00',
  `retailprice` decimal(10,2) NOT NULL default '0.00',
  `weight` decimal(10,2) NOT NULL default '0.00',
  `imageurl` varchar(255) NOT NULL default '',
  `thumbnailurl` varchar(255) NOT NULL default '',
  `sizechartlink` varchar(255) NOT NULL default '',
  `content` text NOT NULL,
  `remark` varchar(100) NOT NULL default '',
  `colorchartlink` varchar(255) default NULL,
  `outofstock` char(1) NOT NULL default '',
  `summary` text NOT NULL,
  `freehandoutlink` varchar(255) default NULL,
  `msrp` decimal(10,2) default NULL,
  `enabled` tinyint(1) NOT NULL default '1',
  `sales_score` float NOT NULL default '0',
  `sales_score_offset` float NOT NULL default '0',
  `date_added` timestamp NULL default CURRENT_TIMESTAMP,
  `brand` varchar(255) default NULL,
  `tag_status` varchar(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `product_retailprice_idx` (`retailprice`),
  KEY `suppliersku` (`suppliersku`),
  FULLTEXT KEY `product_name_summary_ft` (`name`,`summary`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Also, by request, the result of a EXPLAIN:

+----+-------------+---------------------+------+---------------------+----------+---------+-------+-------+------------------------------------------------+
| id | select_type | table               | type | possible_keys       | key      | key_len | ref   | rows  | Extra                                          |
+----+-------------+---------------------+------+---------------------+----------+---------+-------+-------+------------------------------------------------+
|  1 | SIMPLE      | product_xref        | ALL  | IDX_PROD1,IDX_PROD2 | NULL     | NULL    | NULL  | 30035 | Using temporary; Using filesort                |
|  1 | SIMPLE      | product_viewhistory | ref  | cookieId            | cookieId | 92      | const |   682 | Using where                                    |
|  1 | SIMPLE      | product_product     | ALL  | PRIMARY             | NULL     | NULL    | NULL  | 31880 | Range checked for each record (index map: 0x1) |
+----+-------------+---------------------+------+---------------------+----------+---------+-------+-------+------------------------------------------------+
3 rows in set (0.00 sec)

New updated version as I realized I did not need product_viewhistory at all. I was left from older code:

SELECT DISTINCT product_product.id, product_product.name, product_product.retailprice, product_product.imageurl, product_product.thumbnailurl, product_product.msrp
FROM product_product, product_xref
WHERE 
(
(product_xref.product_id_1 IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172) AND product_xref.product_id_2 = product_product.id)
OR 
(product_xref.product_id_2 IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172) AND product_xref.product_id_1 = product_product.id)
)
AND product_product.outofstock='N'
ORDER BY product_xref.hits DESC
LIMIT 10

And the new explain:

+----+-------------+-----------------+-------------+---------------------+---------------------+---------+------+-------+-------------------------------------------------------------------------------------+
| id | select_type | table           | type        | possible_keys       | key                 | key_len | ref  | rows  | Extra                                                                               |
+----+-------------+-----------------+-------------+---------------------+---------------------+---------+------+-------+-------------------------------------------------------------------------------------+
|  1 | SIMPLE      | product_xref    | index_merge | IDX_PROD1,IDX_PROD2 | IDX_PROD1,IDX_PROD2 | 5,5     | NULL |    32 | Using sort_union(IDX_PROD1,IDX_PROD2); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | product_product | ALL         | PRIMARY             | NULL                | NULL    | NULL | 31880 | Range checked for each record (index map: 0x1)                                      |
+----+-------------+-----------------+-------------+---------------------+---------------------+---------+------+-------+-------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Answer 1

The first thing to do is see what MySQL is doing under the hood with EXPLAIN, then go from there. It sounds like you have some indexing to do.

Answer 2

You need to optimize your query. Run it from mysql prompt or mysql client with EXPLAIN and check execution plan. You may need to add indexes to your tables. Keep in mind that if you run this query few times in a row, mysql server will cache results and you shouldn't rely on their fast execution time . Maybe it is the reason why your query runs fine 90% of the time.

Answer 3

I rewrote your query as:

   SELECT DISTINCT
          pp.id, 
          pp.name, 
          pp.retailprice, 
          pp.imageurl, 
          pp.thumbnailurl,
          pp.msrp
     FROM PRODUCT_PRODUCT pp
LEFT JOIN PRODUCT_XREF px1 ON px1.product_id_2 = pp.id
LEFT JOIN PRODUCT_XREF px2 ON px2.product_id_1 = pp.id
    WHERE EXISTS(SELECT NULL
                   FROM PRODUCT_VIEWHISTORY pvh
                  WHERE pvh.productid = px1.product_id_1
                    AND pvh.cookieId = '188af1efad392c2adf82'
                    AND pvh.productId IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172))
       OR EXISTS(SELECT NULL
                   FROM PRODUCT_VIEWHISTORY pvh
                  WHERE pvh.productid = px2.product_id_2
                    AND pvh.cookieId = '188af1efad392c2adf82'
                    AND pvh.productId IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172))
      AND pp.outofstock = 'N'
 ORDER BY GREATEST(px1.hits, px2.hits) DESC
    LIMIT 10

It would've been easier if the ORDER BY didn't rely on the PRODUCT_XREF.hits column. Too bad MySQL doesn't support Common Table Expressions (CTEs)/Subquery Factoring...

Having two different product_id references is a highly questionable approach. I recommend reviewing the data model.