MySQL – Resolving Queries Stuck at Sending Data

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