Increase the speed

  • Resolved goranpro

    (@goranpro)


    7 years, 6 months ago

    Hi,

    i have a woocommerce shop with about 50.000 images (5.000 products).
    The Rename in Bulk is running realy slow. It takes more than 1 minute to porcess 1 image. So this would take 30 days to process all 50k images. Its a pretty fast VPS with SSD hard disk and 16 GB of RAM.

    What could i do to make it faster? Maybe putting indexes on some tables in DB or something like that?

    • This topic was modified 7 years, 6 months ago by goranpro.
    • This topic was modified 7 years, 6 months ago by goranpro.
    • This topic was modified 7 years, 6 months ago by goranpro.
    • This topic was modified 7 years, 6 months ago by goranpro.
Viewing 8 replies - 1 through 8 (of 8 total)
  • Thread Starter goranpro

    (@goranpro)

    7 years, 6 months ago

    Hi,

    I tried to debug this and found that the most problematic update that takes 10 seconds per image is:
    UPDATE wp_postmeta SET meta_value = 'https://www.domain.com/de/wp-content/uploads/2018/06/10754-e5ac98-600x600.jpg' WHERE meta_key <> '_original_filename' AND (TRIM(meta_value) = 'https://www.domain.com/de/wp-content/uploads/2018/06/10754-0c3a60-600x600.jpg' OR TRIM(meta_value) = 'https://www.domain.com/de/wp-content/uploads/2018/06/10754-0c3a60-600x600.jpg' )

    I wanted to know if the problem is the update or the select condition that is inside this update, so i wrote this update as select:
    SELECT * FROM wp_postmeta WHERE meta_key <> '_original_filename' AND (TRIM(meta_value) = 'https://www.domain.com/de/wp-content/uploads/2018/06/10754-0c3a60-600x600.jpg' OR TRIM(meta_value) = 'https://www.domain.com/de/wp-content/uploads/2018/06/10754-0c3a60-600x600.jpg' )

    The result is 10 seconds. So the problem is finding what to update, rather than updating.

    Now i tried to remove the meta_key <> ‘_original_filename’ part. This made the select execute in 1 second (10x improvement). So i tried to write the select in a way where i moved this part into the HAVING at the end:
    SELECT * FROM wp_postmeta WHERE (TRIM(meta_value) = 'https://www.domain.com/de/wp-content/uploads/2018/06/10754-0c3a60-600x600.jpg' OR TRIM(meta_value) = 'https://www.domain.com/de/wp-content/uploads/2018/06/10754-0c3a60-600x600.jpg' ) HAVING meta_key <> '_original_filename'

    This code also executes in 1 second.

    I dont know if this code is correct as all selects always return an empty recordset in my case and i dont know what this update is supposed to do. Can you please take a look?

    • This reply was modified 7 years, 6 months ago by goranpro.
    Plugin Author Jordy Meow

    (@tigroumeow)

    7 years, 6 months ago

    Hi 🙂 Thanks a lot for looking into this. We will also have another look and will come back here later, that might take a bit of time. But that would be really nice if we could optimize this part of the process, indeed 🙂

    Thread Starter goranpro

    (@goranpro)

    7 years, 6 months ago

    Hi,

    did you maybe already had a time to look into this. This long processing times are killing me 🙂

    Do you think I could just remove the “meta_key <> ‘_original_filename'” string from the update if my plan is to run only the bulk rename and only one time?

    • This reply was modified 7 years, 6 months ago by goranpro.
    Plugin Author Jordy Meow

    (@tigroumeow)

    7 years, 6 months ago

    Sorry, things are happening so this issue is not being tackled yet. Of course, you can try that, but I am not sure if it’s a good idea or not. As long as you make a proper backup, you could try everything you like and delete files. I will come back to you on that later.

    Thread Starter goranpro

    (@goranpro)

    7 years, 6 months ago

    Hi,

    I further debugged this and found out that if I remove the TRIM functions the SQL executes in 0.0008 seconds (compared to 10 seconds with TRIM)! Why is there a TRIM function in this SQL? …can I safely remove it?

    • This reply was modified 7 years, 6 months ago by goranpro.
    Thread Starter goranpro

    (@goranpro)

    7 years, 6 months ago

    Ok, some more explanation….

    I achieved this speed by:
    1) Crating an index on “meta_value” column and then explicitely using it in SQL:
    SELECT * FROM wp_postmeta USE INDEX (meta_value) WHERE….
    2) And Removing the TRIM from SQL
    = speed 0.0008 seconds

    …with index and without removing the TRIM speed: 1.8135 seconds.

    So why is there a TRIM function in this SQL? …can I safely remove it?

    • This reply was modified 7 years, 6 months ago by goranpro.
    • This reply was modified 7 years, 6 months ago by goranpro.
    Plugin Author Jordy Meow

    (@tigroumeow)

    7 years, 6 months ago

    It’s tricky, but yes, in most cases I believe this TRIM is not useful. But I need further checks before actually removing it.

    The plugin could also have an index, but I am not sure a plugin has the right to do this (of course, technically it can, and maybe it could be an option).

    amekusa

    (@amekusa)

    7 years, 6 months ago

    Hi, @goranpro.
    The UPDATE query runs only if the option: “SIDE UPDATES > Post Meta” has been enabled. And it suppose to update all the custom fields (except for ‘_original_filename’) of which value references the old filename.

    We haven’t replicate your issue yet. But as far as I can guess from your posts,
    Which condition comes at first is the matter.
    With your modified query, contrary to ours, the condition: ‘meta_key <> _original_filename’ is evaluated at the end.

    I’m afraid we can’t fix the query very soon because we can’t really tell if your case can be applied for every user for sure. We need to have more tests.

    By the way, about your code, I guess ‘HAVING’ clause can also be replaced with ‘AND’ clause if the order was the only the matter. Could you test it?

    Regards,
    Satoshi

Viewing 8 replies - 1 through 8 (of 8 total)

The topic ‘Increase the speed’ is closed to new replies.