如何优化含数百个NOT IN值的WordPress慢查询？

Hey there, let's dig into fixing those slow WordPress queries you're seeing with Percona Server. Even though your wp_posts table is small (under 2400 rows) and server load is low, that NOT IN clause with up to 1000 IDs is likely the bottleneck. Here are actionable steps to optimize things:

First off, let's make sure MySQL isn't doing a full table scan every time. Grab your slow query and run:

EXPLAIN [your slow query here];

Look at the type column—if it says ALL, that means it's scanning every row in wp_posts. The fix here is to add a composite index that covers your query's filter conditions plus the ID column (since you're excluding by ID).

For example, if your query filters by post_type and post_status, create this index:

CREATE INDEX idx_post_type_status_id ON wp_posts (post_type, post_status, ID);

This lets MySQL quickly narrow down the rows that match your post type/status, then efficiently exclude the IDs in your NOT IN list without scanning the whole table.

NOT IN can get sluggish with large ID lists—even 400 items can cause unnecessary overhead. Swap it out for one of these better options:

Option A: Use NOT EXISTS #

This is often more efficient than NOT IN because it short-circuits as soon as it finds a match. Here's how to rewrite your query:

SELECT p.*
FROM wp_posts p
WHERE p.post_type = 'your_post_type' -- Replace with your actual type
  AND p.post_status = 'publish' -- Adjust to your status filter
  AND NOT EXISTS (
      SELECT 1
      FROM (
          SELECT 1 AS id UNION ALL SELECT 2 UNION ALL 
          -- Add all your exclude IDs here, separated by UNION ALL
          SELECT 400
      ) AS exclude_list
      WHERE exclude_list.id = p.ID
  );

Option B: Use a Temporary Table (for 500+ exclude IDs) #

If your exclude list hits 1000 items, a temporary table with a primary key will speed up the join significantly:

-- Create temp table with primary key for fast lookups
CREATE TEMPORARY TABLE exclude_ids (id INT PRIMARY KEY);
-- Insert all your exclude IDs
INSERT INTO exclude_ids VALUES (1), (2), ..., (1000);
-- Run the query with LEFT JOIN + IS NULL
SELECT p.*
FROM wp_posts p
LEFT JOIN exclude_ids e ON p.ID = e.id
WHERE p.post_type = 'your_post_type'
  AND p.post_status = 'publish'
  AND e.id IS NULL;
-- Clean up the temp table
DROP TEMPORARY TABLE exclude_ids;

Percona handles temporary tables efficiently, so this will cut down on the time spent matching exclude IDs.

If this query is coming from custom code or a plugin, ditch raw SQL and use WordPress's native functions—they're optimized and work with WordPress's caching layer:

Use WP_Query with these performance-focused arguments:

$exclude_ids = [1, 2, ..., 400]; // Your exclude list
$args = [
    'post_type' => 'your_post_type',
    'post_status' => 'publish',
    'post__not_in' => $exclude_ids,
    'no_found_rows' => true, // Skip COUNT(*) if you don't need pagination
    'update_post_meta_cache' => false, // Disable if you don't need post meta
    'update_post_term_cache' => false, // Disable if you don't need taxonomy data
];
$optimized_query = new WP_Query($args);

This reduces unnecessary database calls and lets WordPress use object caching (like Redis or Memcached if you have it set up) to serve repeated queries from memory instead of hitting the database.

Even with a low load, make sure your InnoDB buffer pool is sized correctly—this is where Percona caches table data and indexes. Run this to check your current setting:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

For a 2-core server, set this to 50-70% of your available RAM (e.g., 2GB if you have 4GB total). This keeps your wp_posts table and indexes in memory, avoiding slow disk reads.

内容的提问来源于stack exchange，提问作者KDX