WordPress Search Was Quietly Killing a WooCommerce Store's Database

A client's WooCommerce store — roughly 8,000 products, 400 orders a day — started crawling to a halt every afternoon around 14:00. Pages that normally loaded in under a second were taking eight to twelve seconds. The checkout wasn't timing out (yet), but the admin dashboard was nearly unusable and the frontend product pages were visibly sluggish.

The server had headroom. CPU was at 40%, RAM was fine, Redis was running, PHP-FPM had spare workers. Nothing in the obvious places pointed to a cause. But MariaDB's query throughput had fallen off a cliff.

The slow query log told the story

I enabled the MariaDB slow query log with a one-second threshold:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Within thirty minutes, the log had filled with variants of the same query pattern:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
WHERE 1=1
  AND (((wp_posts.post_title LIKE '%wireless charging stand%')
    OR (wp_posts.post_excerpt LIKE '%wireless charging stand%')
    OR (wp_posts.post_content LIKE '%wireless charging stand%')))
  AND wp_posts.post_type IN ('post', 'page', 'product', 'product_variation')
  AND (wp_posts.post_status = 'publish')
ORDER BY wp_posts.post_title LIKE '%wireless charging stand%' DESC,
         wp_posts.post_date DESC
LIMIT 0, 10;

Each query was taking between 3 and 14 seconds. During peak hours, there were dozens of them running concurrently. MariaDB's SHOW PROCESSLIST confirmed it:

SHOW FULL PROCESSLIST;

+-----+------+-----------+--------+---------+------+----------+----------------------------+
| Id  | User | Host      | db     | Command | Time | State    | Info                       |
+-----+------+-----------+--------+---------+------+----------+----------------------------+
| 412 | woo  | localhost | woo_db | Query   |    7 | Sending  | SELECT SQL_CALC_FOUND_ROWS |
| 413 | woo  | localhost | woo_db | Query   |    5 | Sending  | SELECT SQL_CALC_FOUND_ROWS |
| 414 | woo  | localhost | woo_db | Query   |    3 | Sending  | SELECT SQL_CALC_FOUND_ROWS |
| 415 | woo  | localhost | woo_db | Query   |    9 | Sending  | SELECT SQL_CALC_FOUND_ROWS |
| 416 | woo  | localhost | woo_db | Query   |   11 | Sending  | SELECT SQL_CALC_FOUND_ROWS |
+-----+------+-----------+--------+---------+------+----------+----------------------------+

Five concurrent search queries, all chewing through CPU. And this was a quiet moment.

Why WordPress search is broken at scale

WordPress's built-in search is a LIKE '%term%' query against post_title, post_excerpt, and post_content. The leading wildcard (%) means MySQL and MariaDB cannot use a B-tree index. Every single search triggers a full table scan across every row in wp_posts.

On a small blog with 200 posts, nobody notices. On a WooCommerce store with 8,000 products, each product having variations, plus pages, plus posts — the wp_posts table had 47,000 rows. The post_content column alone held hundreds of megabytes of product descriptions, embedded shortcodes, and Gutenberg block markup.

I ran EXPLAIN on one of the slow queries to confirm:

EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
WHERE 1=1
  AND (wp_posts.post_title LIKE '%phone case%')
  AND wp_posts.post_type IN ('post', 'page', 'product', 'product_variation')
  AND (wp_posts.post_status = 'publish')
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10\G

type: ALL
rows: 47382
Extra: Using where; Using filesort

type: ALL — full table scan. Every row examined for every query. The SQL_CALC_FOUND_ROWS directive made it worse: MariaDB had to scan the entire matching set to count total results, even though only 10 rows were returned. No early termination.

Where the traffic was coming from

Before fixing the queries, I needed to understand who was actually searching. I checked the Nginx access logs:

grep "/?s=" /var/log/nginx/access.log | awk '{print $1}' | sort | uniq -c | sort -rn | head -20

The output was revealing:

   2847 66.249.xx.xx
   1923 40.77.xx.xx
   1104 114.119.xx.xx
    847 17.58.xx.xx
    312 192.168.xx.xx

The top three IPs were Googlebot, Bingbot, and Bytespider. Between them, they had hammered the search endpoint over 5,800 times in a single day. Someone had linked an internal search results page in the site's navigation, and the crawlers were dutifully following every /?s= link they found, including paginated variants.

I checked the user agents to confirm:

grep "/?s=" /var/log/nginx/access.log | grep -oP '"[^"]*"$' | sort | uniq -c | sort -rn | head -5

   2891 "Mozilla/5.0 (compatible; Googlebot/2.1; ...)"
   1940 "Mozilla/5.0 (compatible; bingbot/2.0; ...)"
   1127 "Mozilla/5.0 (compatible; Bytespider; ...)"
    203 "Mozilla/5.0 (iPhone; CPU iPhone OS ..."
     89 "Mozilla/5.0 (Windows NT 10.0; ..."

Over 95% of the search traffic was bots, not humans. The store's actual customers were barely using search at all. The database was being crushed by crawlers scanning URLs that should never have been indexed in the first place.

Fix 1: Block bots from the search endpoint

The immediate priority was to stop the bleeding. I added a noindex directive for search results and blocked crawlers from the endpoint via robots.txt:

# robots.txt
Disallow: /?s=
Disallow: /search/

Then I added an Nginx rate limit specifically for the search endpoint:

# In the http block
limit_req_zone $binary_remote_addr zone=search:10m rate=2r/s;

# In the server block
location / {
    if ($arg_s != "") {
        set $is_search 1;
    }

    if ($is_search) {
        rewrite ^ /search-limited last;
    }

    try_files $uri $uri/ /index.php?$args;
}

location = /search-limited {
    internal;
    limit_req zone=search burst=4 nodelay;
    limit_req_status 429;

    rewrite ^ /index.php?$args break;
    include fastcgi_params;
    fastcgi_pass unix:/run/php/php8.3-fpm.sock;
    fastcgi_param SCRIPT_FILENAME $document_root/index.php;
}

This caps any single IP to two search requests per second with a short burst allowance. Legitimate users searching the store never hit this limit. Overeager crawlers get a 429 Too Many Requests response instead of a database-crushing query.

Fix 2: Add a FULLTEXT index

Rate limiting solved the bot problem, but I still needed human search to be fast. WordPress's LIKE '%term%' approach is fundamentally unsalvageable at this table size, so I switched the search mechanism to use MariaDB's built-in FULLTEXT indexing.

First, I confirmed the table was InnoDB (FULLTEXT on InnoDB has been supported since MySQL 5.6 and MariaDB 10.0.5):

SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'woo_db' AND TABLE_NAME = 'wp_posts';

+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+

Then I added the FULLTEXT index:

ALTER TABLE wp_posts ADD FULLTEXT INDEX ft_search (post_title, post_excerpt, post_content);

On a 47,000-row table with sizeable post_content values, this took about 90 seconds. On a live site I would run this during a quiet period — the ALTER TABLE takes a write lock on older MariaDB versions, though 10.6+ supports online DDL for FULLTEXT index creation.

With the index in place, I installed a lightweight plugin that replaces WordPress's default LIKE query with a MATCH ... AGAINST query. The core search filter hook is posts_search:

add_filter('posts_search', function ($search, $wp_query) {
    global $wpdb;

    if (!$wp_query->is_search() || empty($wp_query->query_vars['s'])) {
        return $search;
    }

    $term = $wp_query->query_vars['s'];
    $escaped = $wpdb->prepare('%s', $term);

    $search = " AND MATCH({$wpdb->posts}.post_title, {$wpdb->posts}.post_excerpt, {$wpdb->posts}.post_content) AGAINST({$escaped} IN BOOLEAN MODE) ";

    return $search;
}, 10, 2);

This replaces the three LIKE '%term%' clauses with a single MATCH ... AGAINST that uses the FULLTEXT index. MariaDB now scores results by relevance rather than just checking for substring presence.

The difference

I ran the same search query with EXPLAIN after the change:

EXPLAIN SELECT wp_posts.ID FROM wp_posts
WHERE MATCH(wp_posts.post_title, wp_posts.post_excerpt, wp_posts.post_content)
AGAINST('phone case' IN BOOLEAN MODE)
AND wp_posts.post_type IN ('post', 'page', 'product', 'product_variation')
AND wp_posts.post_status = 'publish'
LIMIT 0, 10\G

type: fulltext
rows: 1
Extra: Using where

type: fulltext instead of type: ALL. The query went from scanning 47,000 rows to using the index directly. Execution time dropped from 3-14 seconds to under 50 milliseconds.

I also removed the SQL_CALC_FOUND_ROWS directive via the found_posts_query filter. WordPress uses this to calculate pagination totals, but it forces the database to count every matching row even when you only need the first 10. For search results, an approximate count or a simple "Next page" link is good enough:

add_filter('found_posts_query', function ($sql, $wp_query) {
    if ($wp_query->is_search()) {
        return '';
    }
    return $sql;
}, 10, 2);

Fix 3: Stop search URLs from being crawled in the first place

The robots.txt change would stop well-behaved crawlers going forward, but I also needed to ensure no search result pages were indexed. I added a noindex meta tag for search pages via the theme:

add_action('wp_head', function () {
    if (is_search()) {
        echo '<meta name="robots" content="noindex, nofollow">';
    }
});

And I removed the internal link to the search results page from the navigation. The search form itself is fine — it's the search results page URLs that crawlers were following and indexing.

After the fixes

Within 24 hours, the bot traffic to /?s= dropped from thousands of daily hits to near zero. The handful of genuine human searches now resolved in under 50ms instead of 3-14 seconds. MariaDB's query throughput returned to normal, and the afternoon slowdowns disappeared entirely.

The monitoring I now keep in place for this site:

# Daily check for search query volume
grep -c "/?s=" /var/log/nginx/access.log

# Weekly slow query audit
mysqladmin -u root extended-status | grep -i "slow_queries"

If the slow query count starts climbing again, I know something has changed.

The broader lesson

WordPress's built-in search was designed for small blogs. It was never meant to handle a product catalogue with thousands of entries and tens of thousands of total rows in wp_posts. Most WooCommerce stores I manage don't have a search performance problem because their customers use category navigation and filters rather than free-text search. But the moment a bot discovers the search endpoint — or a site links to search results internally — the database takes the hit.

For stores with more than 10,000 products, I recommend going further and replacing WordPress search entirely with a dedicated search engine like Meilisearch or Elasticsearch. They build their own inverted indexes, handle typos and fuzzy matching, and never touch your database at query time. But for a catalogue in the low thousands, a FULLTEXT index and proper bot management is often all you need.

If your WooCommerce store feels slow and you can't pinpoint why, check your slow query log for LIKE '% patterns. You might find the same thing I did — a search feature nobody uses, hammered by bots nobody invited, running queries the database was never designed to handle.

---

Stop Firefighting. Start Maintaining.

I manage 70+ WordPress sites for agencies and businesses. Whether you need ongoing maintenance, emergency support, or a one-off performance fix — I can help.

View Maintenance Plans | Get in Touch

Related: WooCommerce Database Meltdown — From 376-Second Queries to Under 1 Second · WordPress Database Optimisation