MySQL(InnoDB)与PSQL前后通配符模糊查询性能优化咨询

Great question! We've all been there—trying to run a wildcard-on-both-ends LIKE '%sonal%' query and watching it grind to a halt because standard B-tree indexes can't help with middle-of-string matches. Let's break down the most effective fixes for both databases:

MySQL (InnoDB) Solutions #

• Use Full-Text Indexes
  InnoDB supports native full-text indexing, purpose-built for text search scenarios. Unlike LIKE, it works with indexed tokens and handles middle-term matches efficiently.

  • Create the index:

    ALTER TABLE your_table ADD FULLTEXT INDEX idx_ft_your_column (your_column);
    

  • Query using boolean mode for exact term matches:

    SELECT * FROM your_table WHERE MATCH(your_column) AGAINST('sonal' IN BOOLEAN MODE);
    

  Note: By default, InnoDB ignores words shorter than 4 characters. Adjust the ft_min_word_len config if you need to search for shorter terms.

• Precompute Tokenized Data
  For custom control over matching (like custom tokenization), split your column's content into individual words/tokens and store them in a separate junction table linked to your main table.

  • Example setup: Create your_table_tokens with token and table_id columns, then populate it by splitting text from your_table.your_column.
  • Query by joining on tokens:

    SELECT DISTINCT t.* FROM your_table t
    JOIN your_table_tokens tt ON t.id = tt.table_id
    WHERE tt.token LIKE '%sonal%';
    

  This turns a full-table scan into an indexed lookup on the token table.

• Integrate a Dedicated Search Engine
  For large datasets or complex needs (synonyms, ranking), tools like Elasticsearch or Solr are worth the setup effort. Sync your MySQL data to the search engine and leverage its optimized full-text capabilities for fast matches.

PostgreSQL Solutions #

• Trigram Indexes (pg_trgm Extension)
  This is PostgreSQL's secret weapon for wildcard matches. The pg_trgm extension creates indexes based on 3-character fragments of your text, letting you use your existing LIKE syntax with index support.

  • Enable the extension first:

    CREATE EXTENSION pg_trgm;
    

  • Create a GIN index (better for large datasets) or GIST index (smaller, faster to build):

    CREATE INDEX idx_trgm_your_column ON your_table USING GIN (your_column gin_trgm_ops);
    

  • Now your original query will use the index:

    SELECT * FROM your_table WHERE your_column LIKE '%sonal%';
    

• Native Full-Text Search
  PostgreSQL has robust full-text features using tsvector (tokenized text) and tsquery (search queries), supporting advanced features like stemming and ranking.

  • Create a GIN index on the tokenized column:

    CREATE INDEX idx_fts_your_column ON your_table USING GIN (to_tsvector('english', your_column));
    

  • Query using the full-text match operator @@:

    SELECT * FROM your_table WHERE to_tsvector('english', your_column) @@ to_tsquery('english', 'sonal');
    

• Dedicated Search Engine Integration
  Just like MySQL, for very large datasets or complex search requirements, syncing to Elasticsearch/Solr will give you the best performance and flexibility.

General Tips #

• Filter First, Search Later: Add other WHERE clauses (date ranges, category filters) to reduce the number of rows you need to run the wildcard search against.
• Avoid Large Text Fields: Extract relevant searchable content into a smaller VARCHAR column—indexes on smaller columns are faster to scan.
• Test for Small Datasets: If your table only has a few thousand rows, a full-table scan might be faster than setting up complex indexes. Don't over-engineer!

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