Many mysql queries stuck at sending data

Question

I am using opencart and I can se many queries blocking the mysql server. All queries are the same except the p.manufacturer_id. Attached there is the query and the explain of the query. The query is standard opencart for getting products.

The same database is running on 2 VM machines on the same server. On one server the query is fast and no problems on the other is stopping.

Any help on why is being blocked?

-- Connection Id: 7847
-- User: autohut
-- Host: localhost
-- DB: autohut_store
-- Command: Query
-- Time: 39
-- State: Sending data
SELECT 
 p.product_id,
 p.price,
 p.old_product_exchange,
 (SELECT 
 AVG(rating) AS total
 FROM
 review r1
 WHERE
 r1.product_id = p.product_id
 AND r1.status = '1'
 GROUP BY r1.product_id) AS rating,
 (SELECT 
 price
 FROM
 product_discount pd2 USE INDEX (ORDERBY)
 WHERE
 pd2.product_id = p.product_id
 AND pd2.customer_group_id = '1'
 AND pd2.quantity = '1'
 AND ((pd2.date_start = '0000-00-00'
 OR pd2.date_start < NOW())
 AND (pd2.date_end = '0000-00-00'
 OR pd2.date_end > NOW()))
 ORDER BY pd2.priority ASC , pd2.price ASC
 LIMIT 1) AS discount,
 (SELECT 
 price
 FROM
 product_special ps USE INDEX (ORDERBY)
 WHERE
 ps.product_id = p.product_id
 AND ps.customer_group_id = '1'
 AND ((ps.date_start = '0000-00-00'
 OR ps.date_start < NOW())
 AND (ps.date_end = '0000-00-00'
 OR ps.date_end > NOW()))
 ORDER BY ps.priority ASC , ps.price ASC
 LIMIT 1) AS special
FROM
 product p
 LEFT JOIN
 product_description pd ON (p.product_id = pd.product_id)
WHERE
 pd.language_id = '2' AND p.status = '1'
 AND p.date_available <= NOW()
 AND EXISTS( SELECT 
 product_id
 FROM
 product_to_store p2s
 WHERE
 p.product_id = p2s.product_id
 AND p2s.store_id = '0')
 AND p.manufacturer_id = '11'
GROUP BY p.product_id
ORDER BY price != 0 DESC , price ASC , (CASE
 WHEN special IS NOT NULL THEN special
 WHEN discount IS NOT NULL THEN discount
 ELSE p.price
END) ASC
LIMIT 5383 , 7

And this is the explain:

+- TEMPORARY
 table temporary(p,pd,p2s,ps,<none>,r1)
 +- DEPENDENT SUBQUERY
 +- Filter with WHERE
 | +- Bookmark lookup
 | +- Table
 | | table r1
 | | possible_keys product_id
 | +- Index lookup
 | key r1->product_id
 | possible_keys product_id
 | key_len 4
 | ref func
 | rows 1
 +- SUBQUERY
 +- IMPOSSIBLE
 | warning Impossible WHERE noticed after reading const tables
 +- DEPENDENT SUBQUERY
 +- Filter with WHERE
 | +- Bookmark lookup
 | +- Table
 | | table ps
 | +- Index scan
 | key ps->orderBy
 | key_len 11
 | rows 1
 +- DEPENDENT SUBQUERY
 +- Unique index lookup
 | key p2s->PRIMARY
 | possible_keys PRIMARY
 | key_len 8
 | ref autohut_store.p.product_id,const
 | rows 1
 +- JOIN
 +- Unique index lookup
 | key pd->PRIMARY
 | possible_keys PRIMARY
 | key_len 8
 | ref autohut_store.p.product_id,const
 | rows 1
 +- Filter with WHERE
 +- Bookmark lookup
 +- Table
 | table p
 | possible_keys PRIMARY,uc_PersonID,manufacturer_id,active
 +- Index lookup
 key p->manufacturer_id
 possible_keys PRIMARY,uc_PersonID,manufacturer_id,active
 key_len 4
 ref const
 rows 40752

This is the output of the show processlist;

29751 autohut localhost autohut_store Query 14913 Sending data SELECT p.product_id, p.price,p.old_product_exchange, (SELECT AVG(rating) AS total FROM review r1 WHE
29873 autohut localhost autohut_store Query 14544 Sending data SELECT p.product_id, p.price,p.old_product_exchange, (SELECT AVG(rating) AS total FROM review r1 WHE
30102 autohut localhost autohut_store Query 14172 Sending data SELECT p.product_id, p.price,p.old_product_exchange, (SELECT AVG(rating) AS total FROM review r1 WHE
34613 autohut localhost autohut_store Query 5700 Sending data SELECT p.product_id, p.price,p.old_product_exchange, (SELECT AVG(rating) AS total FROM review r1 WHE
34614 autohut localhost autohut_store Query 1977 Sending data SELECT p.product_id, p.price,p.old_product_exchange, (SELECT AVG(rating) AS total FROM review r1 WHE
37631 autohut localhost autohut_store Query 1717 Sending data SELECT p.product_id, p.price,p.old_product_exchange, (SELECT AVG(rating) AS total FROM review r1 WHE
38010 autohut localhost autohut_store Query 1271 Sending data SELECT p.product_id, p.price,p.old_product_exchange, (SELECT AVG(rating) AS total FROM review r1 WHE
38372 autohut localhost autohut_store Query 900 Sending data SELECT p.product_id, p.price,p.old_product_exchange, (SELECT AVG(rating) AS total FROM review r1 WHE
38530 autohut localhost autohut_store Query 722 Sending data SELECT p.product_id, p.price,p.old_product_exchange, (SELECT AVG(rating) AS total FROM review r1 WHE
38808 autohut localhost autohut_store Query 529 Sending data SELECT p.product_id, p.price,p.old_product_exchange, (SELECT AVG(rating) AS total FROM review r1 WHE
39024 root localhost:42168 autohut_store Sleep 312 
39025 root localhost:42169 autohut_store Query 0 SHOW PROCESSLIST
39034 root localhost:42171 Sleep 117 
39036 root localhost:42172 Sleep 1 
39171 autohut localhost autohut_store Query 32 Sending data SELECT DISTINCT(pe.product_id), (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = pe
39211 autohut localhost autohut_store Sleep 185 
39388 autohut localhost autohut_store Sleep 0 

• vfedorkov: Subqueries are often very expensive. Could you please provide SHOW PROCESSLIST; output from the time when MySQL is slow?
• hypemedia: I did provide you the info above but again if it works on a server it should also work on the other one.
• vfedorkov: Servers can run with the different configuration. Subqueries forces MySQL to create temporary table which can be in memory or on disk. Access time for on-disk tmp tables can easily be 1000x slower than for in-memory. You may want to check Created_tmp_tables and Created_tmp_disk_tables from SHOW GLOBAL STATUS to see if this is the case. You can do this manually or using github.com/vlad-github/mysql-health-check

Answer 1

The problem was related to an index product_discount pd2 USE INDEX (ORDERBY) and product_special ps USE INDEX (ORDERBY) after removing the USE INDEX (ORDERBY) everything works perfect. I am not sure why it did not worked with the index but is working without.