2

In a webapp I'm working on, there's an UPDATE that's slowing / blocking out several other queries:

UPDATE products
SET visible = 1
WHERE merchant_id = in_merchant_id
    AND source_code IN (
        SELECT source_code
        FROM feeds_processing_data
        WHERE processing_id = in_processing_id
        )

It basically updates a specific "window" on the products table, turning visibiliy on or off based on some other info. That table is central to the webapp, searches from the frontend are run on in (either via FULLTEXT or by code lookups), and background processes update its contents from time to time.
I usually approach these issues by checking the query plan and verifying that proper indexes are there.

What puzzles me, though, is that listing sessions from information_schema.PROCESSLIST shows that the above query sits in the preparing state, while the other blocked queries are Waiting for table flush, something I have little to no knowledge about.

This is one of those blocked queries:

SELECT id,
    [...]
FROM (
    SELECT (MATCH(NAME, description, manufacturer_name) AGAINST('+crema +mani' IN BOOLEAN MODE)) AS rank,
        p.*
    FROM products p
    WHERE visible = 1
        AND MATCH(NAME, description, manufacturer_name) AGAINST('+crema +mani' IN BOOLEAN MODE) LIMIT 400
    ) ranked
ORDER BY final_price

At a first glance, I see no problems with that UPDATE, this is its EXPLAIN plan:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY products    range   uk_merchant_id_source_code  uk_merchant_id_source_code  4   const   3861    Using where
2   DEPENDENT SUBQUERY  feeds_processing_data   ref fk_processing,source_code   fk_processing   4   const   2   Using where

Indexes on the involved tables follow.

SHOW INDEXES FROM products:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
products    0   PRIMARY 1   id  A   409324  NULL    NULL        BTREE       
products    0   uk_merchant_id_source_code  1   merchant_id A   126 NULL    NULL        BTREE       
products    0   uk_merchant_id_source_code  2   source_code A   409324  NULL    NULL        BTREE       
products    1   visible 1   visible A   2   NULL    NULL        BTREE       
products    1   gtin    1   gtin    A   409324  NULL    NULL    YES BTREE       
products    1   industry_code   1   industry_code   A   409324  NULL    NULL    YES BTREE       
products    1   name_description_manufacturer_name  1   name    NULL    409324  NULL    NULL    YES FULLTEXT        
products    1   name_description_manufacturer_name  2   description NULL    409324  NULL    NULL    YES FULLTEXT        
products    1   name_description_manufacturer_name  3   manufacturer_name   NULL    409324  NULL    NULL    YES FULLTEXT        

SHOW INDEXES FROM feeds_processing_data:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
feeds_processing_data   0   PRIMARY 1   id  A   10336188    NULL    NULL        BTREE       
feeds_processing_data   1   fk_processing   1   processing_id   A   2981    NULL    NULL        BTREE       
feeds_processing_data   1   source_code 1   source_code A   136002  NULL    NULL    YES BTREE       
feeds_processing_data   1   rejected    1   rejected    A   1   NULL    NULL        BTREE       

The first question I can't address is: why is it taking so much to complete the prepare phase?
Then, how could I further inspect the issue? What informations am I missing here?

6
  • Your indexes could be fragmented or worse. Please run optimize for each table (if u can). Then do a query plan. Commented Nov 19, 2015 at 15:28
  • Is the table (and other ones) MyISAM or InnoDB?
    – jkavalik
    Commented Nov 20, 2015 at 8:22
  • @jkavalik They're all InnoDB.
    – watery
    Commented Nov 20, 2015 at 13:48
  • @greenlitmysql I'll do it on those tables ASAP. I'm trying that command on a test database, and I see it is taking several minutes: how can I tell whether a table needs to be OPTIMIZEd? Or should I just run it on a regular basis?
    – watery
    Commented Nov 20, 2015 at 13:49
  • Regular OPTIMIZE is not a good idea on InnoDB tables - it forces a complete rebuild of the table which may take hours for big (multi-gigabyte) tables. What's the MySQL version? The update might be in prepare when locating the rows to update by the select - is the select fast? You should rewrite the subquery to a join instead - dependent subquery is executed once per row, which is a lot. Waiting for table flush is a different matter, check the manual for possible reasons. Maybe automatic ANALYZE?
    – jkavalik
    Commented Nov 20, 2015 at 14:05

1 Answer 1

5

As @jkavalik wrote you're executing the update with a dependent subquery which effectively runs it 3861 times making it highly ineffective even if the query itself runs fast.

An updated version of the query could be:

UPDATE products
JOIN feeds_processing_data ON 
   (product.source_code = feeds_processing_data.source_code 
    AND ?.processing_id = ?.in_processing_id)
SET products.visible = 1
WHERE merchant_id = in_merchant_id

You didn't attach the table schemas so I just assume this would work. You might need to fully qualify the field names (using tablename.fieldname). Also feeds_processing_data could benefit from a composite index covering the involved fields.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.