CODEWITHSUNDEEP
mysqlwordpressperformancesql-update
Sjoerd
Sjoerd

Reputation: 84

MySQL key-value table update many rows for many ID's at once

I'm currently struggling to get a MySQL multi-line UPDATE query as fast as possible.

Maybe it comes in handy to know that i'm currently using Wordpress and this query updates the wp_postmeta table.

The query I currently have takes 184 seconds. I really need to speed it up because this query will run multiple times in a row to update products from a CSV sheet.

The products are imported in batches of 50 products.

Ofcourse, if any more information is necessary please let me know. Hope someone is willing and able to help. Thanks!

UPDATE wp_postmeta t1
LEFT JOIN wp_wc_importer_product_cache t2 ON t1.post_id=t2.product_id
LEFT JOIN wp_wc_importer_csv t3 ON t2.sku=t3.sku AND t2.ean = t3.ean
    -- Global meta
    SET t1.meta_value = (
        CASE WHEN t1.meta_key = '_price' AND (t1.meta_value != t3.sell_price OR (t1.meta_value IS NULL AND t3.sell_price IS NOT NULL)) THEN t3.sell_price
             WHEN t1.meta_key = '_regular_price' AND (t1.meta_value != t3.sell_price OR (t1.meta_value IS NULL AND t3.sell_price IS NOT NULL)) THEN t3.sell_price
             WHEN t1.meta_key = '_tax_status' AND (t1.meta_value != 'taxable' OR (t1.meta_value IS NULL AND 'taxable' IS NOT NULL)) THEN 'taxable'
             WHEN t1.meta_key = '_backorders' AND (t1.meta_value != 'no' OR (t1.meta_value IS NULL AND 'no' IS NOT NULL)) THEN 'no'
             WHEN t1.meta_key = '_weight' AND (t1.meta_value != t3.weight OR (t1.meta_value IS NULL AND t3.weight IS NOT NULL)) THEN t3.weight
             WHEN t1.meta_key = '_product_version' AND (t1.meta_value != '3.3.1' OR (t1.meta_value IS NULL AND '3.3.1' IS NOT NULL)) THEN '3.3.1'
             ELSE CASE WHEN t2.sku_parent = '' THEN
                -- Parent meta
                CASE WHEN t1.meta_key = 'product_title' AND (t1.meta_value != t3.title_dutch OR (t1.meta_value IS NULL AND t3.title_dutch IS NOT NULL)) THEN t3.title_dutch
                     WHEN t1.meta_key = 'article_number' AND (t1.meta_value != t3.iso OR (t1.meta_value IS NULL AND t3.iso IS NOT NULL)) THEN t3.iso
                     WHEN t1.meta_key = '_tax_class' AND (t1.meta_value != '' OR (t1.meta_value IS NULL AND '' IS NOT NULL)) THEN ''
                     WHEN t1.meta_key = 'product_seo_text' AND (t1.meta_value != t3.desc_long_dutch OR (t1.meta_value IS NULL AND t3.desc_long_dutch IS NOT NULL)) THEN t3.desc_long_dutch
                     ELSE t1.meta_value
                END
             ELSE
                -- Variation meta
                CASE WHEN t1.meta_key = '_variation_description' AND (t1.meta_value != t3.invoice_description OR (t1.meta_value IS NULL AND t3.invoice_description IS NOT NULL)) THEN t3.invoice_description
                     WHEN t1.meta_key = 'attribute_pa_material' AND (t1.meta_value != t3.material OR (t1.meta_value IS NULL AND t3.material IS NOT NULL)) THEN t3.material
                     WHEN t1.meta_key = 'attribute_pa_diameter' AND (t1.meta_value != IF(t3.diameter_prefix != '', CONCAT(t3.diameter_prefix, ' ', t3.diameter), t3.diameter) OR (t1.meta_value IS NULL AND t3.diameter IS NOT NULL))
                        THEN IF(t3.diameter_prefix != '', CONCAT(t3.diameter_prefix, ' ', t3.diameter), t3.diameter)
                     WHEN t1.meta_key = 'attribute_pa_length' AND (t1.meta_value != t3.length OR (t1.meta_value IS NULL AND t3.length IS NOT NULL)) THEN t3.length
                     WHEN t1.meta_key = 'attribute_pa_sold-per' AND (t1.meta_value != t3.package_quantity OR (t1.meta_value IS NULL AND t3.package_quantity IS NOT NULL)) THEN  t3.package_quantity
                     WHEN t1.meta_key = 'attribute_pa_screw-thread' AND (t1.meta_value != t3.screw_thread OR (t1.meta_value IS NULL AND t3.screw_thread IS NOT NULL)) THEN t3.screw_thread
                     WHEN t1.meta_key = 'attribute_factuur-beschrijving' AND (t1.meta_value != t3.invoice_description OR (t1.meta_value IS NULL AND t3.invoice_description IS NOT NULL)) THEN t3.invoice_description
                     WHEN t1.meta_key = 'attribute_ean' AND (t1.meta_value != t3.ean OR (t1.meta_value IS NULL AND t3.ean IS NOT NULL)) THEN t3.ean
                     WHEN t1.meta_key = 'attribute_sp-artikel-nummer' AND (t1.meta_value != t3.sp_article_number OR (t1.meta_value IS NULL AND t3.sp_article_number IS NOT NULL)) THEN t3.sp_article_number
                     WHEN t1.meta_key = '_stock' AND (t1.meta_value != t3.stock AND t3.stock != '' AND t3.stock IS NOT NULL OR (t1.meta_value IS NULL AND t3.stock IS NOT NULL)) THEN t3.stock
                     WHEN t1.meta_key = '_stock_status' AND t3.stock != '' AND t3.stock IS NOT NULL THEN
                        CASE WHEN t3.stock > 0 THEN 'instock' ELSE 'outofstock' END
                     ELSE t1.meta_value
                END
            END
        END
    )
WHERE t1.post_id IN (52558,52559,52560,52561,52562,52563,52564,52565,52566,52567,52568,52569,52570,52571,52572,52573,52574,52575,52576,52577,52578,52579,52580,52581,52582,52583,52584,52585,52586,52587,52588,52589,52590,52591,52592,52593,52594,52595,52596,52597,52598,52599,52600,52601,52608,52609,52610,52611,52612,52613)

-- EDIT (regarding the question of M Khalid Junaid) --

The tables are indexed as follows:

wp_postmeta wp_postmeta

wp_wc_importer_csv wp_wc_importer_csv

wp_wc_importer_product_cache wp_wc_importer_product_cache

Upvotes: 0

Views: 76

Answers (2)

Rick James
Rick James

Reputation: 142453

  1. Write individual UPDATEs for each case.

  2. Wrap them in a transaction (BEGIN...COMMIT)

  3. Follow the tips here for improving the indexes of postmeta.

  4. Rethink the use of LEFT.

  5. Show us SHOW CREATE TABLE for t2 and t3 -- they may need some new composite indexes. The SHOW INDEXES is not adequate. The best indexes are missing.

  6. If you are running a new enough version of MySQL, provide EXPLAIN UPDATE ...

Upvotes: 0

amaksr
amaksr

Reputation: 7745

It looks like this part of the query:

LEFT JOIN wp_wc_importer_csv t3 ON t2.sku=t3.sku AND t2.ean = t3.ean

does full scan of wp_wc_importer_csv on every updated row. Try to add index to wp_wc_importer_csv on (sku, ean) fields.

Upvotes: 1

Related Questions