电商网站多列关键词搜索SQL性能优化求助

Hey there, I totally get how frustrating this must be—spending a month troubleshooting this with ADHD can feel extra draining, so let’s break this down into simple, actionable fixes to speed up your search and simplify your queries.

The Core Problem: Why Your REGEXP Queries Are Slow #

Your current REGEXP approach can’t use MySQL’s indexes effectively. When you use REGEXP across multiple columns with OR, MySQL has to scan almost every row in the table to check each condition—this is why it’s taking 45 seconds for 98k rows. Regular indexes don’t help here because REGEXP doesn’t match prefixes or exact values that indexes are optimized for.

Solution 1: Use MySQL Full-Text Indexing (FULLTEXT) #

MySQL’s FULLTEXT index is built exactly for this kind of multi-column, full-word search. It creates an inverted index that lets you search across multiple columns quickly, and it automatically handles word boundary matching (so you don’t need those messy REGEXP patterns for whole words).

Step 1: Create a Full-Text Index #

First, add a combined full-text index across all the columns you want to search:

ALTER TABLE the_table 
ADD FULLTEXT INDEX ft_search (title, description, child_value, colors);

This index works with title (varchar), description (text), child_value, and colors—MySQL supports full-text indexes on both text and varchar columns.

Step 2: Rewrite Your Search Queries #

Replace all those verbose REGEXP conditions with a single MATCH() AGAINST() clause. This handles whole-word matching automatically and is way faster.

Single Keyword Example (CABLE)

SELECT `id`,`title`,`colors`, `child_value`, `vendor`,`price`,`image1`,`shipping` 
FROM `the_table` 
WHERE `display` = '1' 
  AND `category` = '12' 
  AND MATCH(title, description, child_value, colors) 
      AGAINST('CABLE' IN BOOLEAN MODE);

The IN BOOLEAN MODE ensures we get exact whole-word matches—just like your original REGEXP pattern, but without all the extra syntax.

Multiple Keywords Example (RED CABLE)

For "RED CABLE" (where both words must be present), use the + operator to enforce required terms:

SELECT `id`,`title`,`colors`, `child_value`, `vendor`,`price`,`image1`,`shipping` 
FROM `the_table` 
WHERE `display` = '1' 
  AND `category` = '12' 
  AND MATCH(title, description, child_value, colors) 
      AGAINST('+RED +CABLE' IN BOOLEAN MODE);

This is infinitely simpler than writing dozens of REGEXP conditions! If you want to match either word instead of both, just remove the + signs: AGAINST('RED CABLE' IN BOOLEAN MODE).

Solution 2: Optimize Pagination (Avoid Repeating COUNT(*) Queries) #

Instead of running a separate COUNT(*) query to get total rows for pagination, use MySQL’s SQL_CALC_FOUND_ROWS to get both the results and the total count in one go:

-- First, get your paginated results
SELECT SQL_CALC_FOUND_ROWS 
       `id`,`title`,`colors`, `child_value`, `vendor`,`price`,`image1`,`shipping` 
FROM `the_table` 
WHERE `display` = '1' 
  AND `category` = '12' 
  AND MATCH(title, description, child_value, colors) 
      AGAINST('+RED +CABLE' IN BOOLEAN MODE)
LIMIT 0, 12;

-- Then get the total number of matching rows
SELECT FOUND_ROWS();

This is more efficient than running two separate queries because MySQL calculates the total count while executing the first query.

Bonus: Verify Index Usage #

To make sure MySQL is using your full-text index, run EXPLAIN before your query:

EXPLAIN
SELECT `id`,`title`,`colors`, `child_value`, `vendor`,`price`,`image1`,`shipping` 
FROM `the_table` 
WHERE `display` = '1' 
  AND `category` = '12' 
  AND MATCH(title, description, child_value, colors) 
      AGAINST('CABLE' IN BOOLEAN MODE);

Look for fulltext in the type column—this confirms the index is being used.

Final Notes #

• You can drop your old individual indexes on title and description if you don’t need them for other queries—they won’t help with full-text search.
• Start small: Test the single keyword query first to see the speed improvement (it should drop from 45 seconds to milliseconds). Then add multi-keyword support and pagination.

Take it step by step—you’ve already put in so much work, and this change will make a huge difference.

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