Slow SQL Queries

  • Resolved pixeldevs

    (@pixeldevs)


    2 years, 2 months ago

    In your file pmxi_findDuplicates.php lines 112 – 130 you’re doing a SQL query to look for duplicates by title. This query is “ok” for sites less than 100,000 posts. Once you get over that number, it literally takes 10 seconds per post to find a duplicate. The reason is this line
    AND REPLACE(REPLACE(REPLACE($field, ‘ ‘, ”), ‘\\t’, ”), ‘\\n’, ”) = %s

    Replace in SQL is VERY slow. I know you’re trying to get a more exact match by eliminating spaces, returns, etc, but it causes the import to take HOURS if we’re trying to import / update (in our case) 4500 records in the file. By removing the replaces and changing line 127 to remove the preg_replace, the entire record is loaded, searched for, found, and updated in less that a second.

    ALSO, PLEASE index your post_id and import_id on the wp_pmxi_hash table! We’ve currently got over 1.3 million records in that table and without the (added) index it takes 2 seconds to process. With the index it’s 60ms.

    The page I need help with: [log in to see the link]

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

  • the import i’m struggling with is 11 hours for only ~2600 products. it’s so frustrating.

    Plugin Author WP All Import

    (@wpallimport)

    Hey @pixeldevs,

    Thanks for the suggestions and detailed report. I see that you’ve already sent this via our support system as well, and we’ve posted it internally, so we will discuss the changes with our developers to see if we can implement them in a future release.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Slow SQL Queries’ is closed to new replies.

Tags