I did a query in phpmyadmin using the following command:-

EXPLAIN 
SELECT COUNT(*) 
FROM wp_options 
WHERE option_name LIKE 'wc_facebook_background_product_sync_job_%' 
AND (   option_value LIKE '%\"status\":\"queued\"%' 
     OR option_value LIKE '%\"status\":\"processing\"%' )

Result:

*** row 1 ***
          table:  wp_options
           type:  ALL
  possible_keys:  option_name
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  590455
          Extra:  Using where

What I need to know is how do I delete all of these rows:-

When I tried this :-

delete FROM wp_options 
WHERE option_name LIKE 'wc_facebook_background_product_sync_job_%'
 AND (   option_value LIKE '%\"status\":\"queued\"%'
      OR option_value LIKE '%\"status\":\"processing\"%' )

If only delete 2066 rows and not all the 590544 rows

What could be the problem ! ! am I using the delete query wrongly !

CodePudding user response：

Here's the thing. EXPLAIN gives a count of the rows MySQL's query planner estimates it must examine and pass through your WHERE filter to return your result set.

COUNT(*) gives a count of the rows actually passing through the filter. That is the number of rows that match your background sync filter. And it is the number of rows you will delete. (Approximately if you're working on a live system.)

That ~600K row-count result from EXPLAIN tells you two things.

1. About MySQL: The query must examine many rows. It's not ridiculously surprising that MySQL's query planner decided to do a table scan to satisfy your query. But that's not as important as ...

2. About your WordPress installation: Your wp_options table contains an absurdly large number of rows. It seems likely you use, or formerly used, a plugin that was absurdly sloppy in its cleanup of options. You need to deal with this problem because wp_options gets used a lot by WP.

   A good place to start is by using Advanced Database Cleaner or a similar plugin to clean out "expired transients" -- that is, wp_options rows containing ephemeral data.

   That plugin and some others have paid versions with features to clean out orphaned (unused options).

You can use this query to look at your options table. Almost all option names have a prefix identifying the plugin or subsystem using them. This shows you the most common prefixes. It should help you identify the subsystem creating most of your options.

SELECT COUNT(*) num,
       LEFT(option_name, 24) option_name_prefix,
       MAX(option_name) option_name_example
  FROM wp_options
 GROUP BY LEFT(option_name, 24)
 ORDER BY COUNT(*) DESC

When you have guessed which plugin or plugins are the ones with too many options, go to https://wordpress.stackexchange.com/ and ask for advice on cleaning them up.

Be careful DELETEing options. You can break things.

CodePudding user response：

I finally just issued this mysql command

DELETE
FROM wp_options 
WHERE option_name LIKE 'wc_facebook_background_product_sync_job_%' 

And it deleted the required rows ! !