Speeding Up a slow MySQL SubQuery Update

Question

I have the following UPDATE script which keeps a count of the active products on my site so I can quickly reference if the category has products or not without doing this count on the front end.

UPDATE category_to_store
SET products = (
                SELECT COUNT(*)
                FROM product p 
                LEFT JOIN product_to_category p2c 
                     ON (p.product_id = p2c.product_id) 
                LEFT JOIN product_to_store p2s 
                     ON (p.product_id = p2s.product_id) 
                WHERE p.status = '1' 
                AND p.date_available <= NOW()
                AND p2c.category_id = category_to_store.category_id
                AND p2s.store_id = category_to_store.store_id
               );

My tables used are explained below:

DESCRIBE category_to_store;

Field       Type        Null    Key     Default Extra 
---------------+---------------+-------+-------+-------+---------------
category_id int(11)     NO  PRI     
store_id    int(11)     NO  PRI     
products    int(11)     NO      0   

DESCRIBE product;

Field       Type        Null    Key     Default Extra 
---------------+---------------+-------+-------+-------+---------------
product_id  int(11)     NO  PRI     auto_increment
~
date_available  date        NO          
~
status      tinyint(1)  NO      0               
~

DESCRIBE product_to_category;

Field       Type        Null    Key     Default Extra 
---------------+---------------+-------+-------+-------+---------------
product_id  int(11) NO  PRI     
category_id int(11) NO  PRI     

DESCRIBE product_to_store;

Field       Type        Null    Key     Default Extra 
---------------+---------------+-------+-------+-------+---------------
product_id  int(11) NO  PRI     
store_id    int(11) NO  PRI 0

(product table has fields I've not included that aren't being used)

This currently runs correctly but it takes 110 seconds currently.

I have set the site up to use a WHERE category_to_store.category_id = '(int)' to limit the query but this means working out which categories may have been affected with the update which works I guess but I was wondering if any of you lovely geniuses have any better solutions I have missed?

Thank you in advance.

Answer 1

If you are updating a lot of rows, it might be more efficient to do a join, rather than using a "nested loops" operation.

   UPDATE category_to_store cts
     LEFT 
     JOIN (
            SELECT COUNT(*) AS cnt_
                 , p2c.category_id
                 , p2s.store_id
              FROM product p
              LEFT
              JOIN product_to_category p2c
                ON (p.product_id = p2c.product_id)
              LEFT
              JOIN product_to_store p2s
                ON (p.product_id = p2s.product_id) 
             WHERE p.status = '1'
               AND p.date_available <= NOW()
             GROUP
                BY p2c.category_id
                 , p2s.store_id
          ) c
         ON c.category_id = cts.category_id
        AND c.store_id = cts.store_id
        SET cts.products = IFNULL(c.cnt_,0)

---

For optimum performance, you want suitable indexes available, you might want to consider adding an index e.g.

 ... ON product (product_id, status, date_available)