Full-Text Search in SQL Databases: MySQL and PostgreSQL Compared

Users expect search to just work. Type a few words, get relevant results instantly. While dedicated search engines like Elasticsearch are powerful, you can build surprisingly good search features using just your SQL database.

Let's explore how to implement full-text search in MySQL and PostgreSQL.

Why Use Database Full-Text Search?

Before adding another service to your stack, consider what your database can do:

Advantages:

• No additional infrastructure to maintain
• Data is always in sync (no indexing delays)
• Simpler architecture
• Often "good enough" for many applications

Best for:

• Small to medium datasets (under 10 million records)
• Basic search requirements
• Applications where simplicity matters

Full-Text Search in MySQL

Setting Up

First, create a full-text index:

-- Create table with full-text index
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    author VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_articles (title, body)
);

-- Or add to existing table
ALTER TABLE articles
ADD FULLTEXT INDEX ft_articles (title, body);

Basic Search

Use MATCH ... AGAINST for searching:

-- Simple search
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('database performance');

This returns articles containing "database" or "performance", ranked by relevance.

Search Modes

MySQL offers three search modes:

Natural Language Mode (default):

-- Searches for the words naturally
SELECT title, MATCH(title, body) AGAINST('mysql optimization') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('mysql optimization')
ORDER BY relevance DESC;

Boolean Mode:

-- More control over search logic
SELECT title
FROM articles
WHERE MATCH(title, body) AGAINST('+mysql +optimization -slow' IN BOOLEAN MODE);

Boolean operators:

• + - Must include this word
• - - Must exclude this word
• * - Wildcard (prefix matching)
• "" - Exact phrase
• > - Increase relevance
• < - Decrease relevance

-- Examples of boolean searches
-- Articles about MySQL that mention optimization, but not "slow query"
AGAINST('+mysql +optimization -"slow query"' IN BOOLEAN MODE)

-- Articles about database, preferring those about performance
AGAINST('+database >performance' IN BOOLEAN MODE)

-- Prefix matching for autocomplete
AGAINST('data*' IN BOOLEAN MODE)

Query Expansion Mode:

-- Finds related terms automatically
SELECT title
FROM articles
WHERE MATCH(title, body) AGAINST('database' WITH QUERY EXPANSION);

This first searches for "database", then uses words from the top results to expand the search.

Getting Relevance Scores

SELECT
    id,
    title,
    MATCH(title, body) AGAINST('mysql performance') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql performance')
ORDER BY score DESC
LIMIT 10;

Highlighting Matches

MySQL doesn't have built-in highlighting, but you can simulate it:

SELECT
    id,
    title,
    REPLACE(
        SUBSTRING(body, 1, 200),
        'mysql',
        '<strong>mysql</strong>'
    ) AS snippet
FROM articles
WHERE MATCH(title, body) AGAINST('mysql' IN BOOLEAN MODE)
LIMIT 10;

Full-Text Search in PostgreSQL

PostgreSQL has more powerful full-text search capabilities built in.

Setting Up

Create a text search index:

-- Add a tsvector column for faster searching
ALTER TABLE articles
ADD COLUMN search_vector tsvector;

-- Populate the search vector
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || body);

-- Create an index on the vector
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Keep it updated automatically
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

Basic Search

Use @@ operator to match:

-- Simple search
SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & performance');

Query Syntax

PostgreSQL uses different syntax for search queries:

-- AND operator
to_tsquery('mysql & optimization')

-- OR operator
to_tsquery('mysql | postgresql')

-- NOT operator
to_tsquery('database & !slow')

-- Phrase search (words in order)
phraseto_tsquery('english', 'database optimization')

-- Plain text search (any words)
plainto_tsquery('english', 'database optimization tips')

-- Prefix matching
to_tsquery('data:*')

Ranking Results

PostgreSQL has built-in ranking functions:

SELECT
    id,
    title,
    ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & optimization') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

For more control, use ts_rank_cd which considers document length:

SELECT
    id,
    title,
    ts_rank_cd(search_vector, query, 32) AS rank  -- 32 = normalize by document length
FROM articles, plainto_tsquery('english', 'database tips') query
WHERE search_vector @@ query
ORDER BY rank DESC;

Highlighting Matches

PostgreSQL has native highlighting:

SELECT
    id,
    title,
    ts_headline('english', body, query,
        'StartSel=<strong>, StopSel=</strong>, MaxWords=35, MinWords=15'
    ) AS snippet
FROM articles, plainto_tsquery('english', 'optimization') query
WHERE search_vector @@ query
LIMIT 10;

Combining with Other Filters

SELECT
    id,
    title,
    ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'mysql') query
WHERE search_vector @@ query
  AND created_at > NOW() - INTERVAL '30 days'
  AND author = 'John Doe'
ORDER BY rank DESC
LIMIT 10;

Head-to-Head Comparison

Performance Tips

1. Use Dedicated Search Columns

Don't search across too many columns:

-- Instead of searching title + body + tags + description
-- Create a combined searchable column
ALTER TABLE articles ADD COLUMN searchable TEXT;

UPDATE articles
SET searchable = CONCAT_WS(' ', title, body, tags);

CREATE FULLTEXT INDEX idx_searchable ON articles(searchable);

2. Limit Result Sets

Always paginate search results:

-- MySQL
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('database')
LIMIT 20 OFFSET 0;

-- PostgreSQL
SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('database')
LIMIT 20 OFFSET 0;

3. Consider Minimum Word Length

MySQL ignores words shorter than 4 characters by default. Adjust if needed:

# my.cnf
ft_min_word_len = 3
innodb_ft_min_token_size = 3

4. Keep Statistics Updated

-- MySQL
OPTIMIZE TABLE articles;

-- PostgreSQL
VACUUM ANALYZE articles;

Building an Autocomplete Feature

MySQL Autocomplete

SELECT DISTINCT title
FROM articles
WHERE MATCH(title) AGAINST('data*' IN BOOLEAN MODE)
LIMIT 10;

PostgreSQL Autocomplete

SELECT DISTINCT title
FROM articles
WHERE search_vector @@ to_tsquery('data:*')
LIMIT 10;

Better Autocomplete with Prefix Index

For very fast autocomplete, consider a separate approach:

-- Create a keywords table
CREATE TABLE search_keywords (
    keyword VARCHAR(100) PRIMARY KEY,
    frequency INT DEFAULT 1
);

-- Populate with common search terms
INSERT INTO search_keywords (keyword, frequency)
SELECT word, COUNT(*) as freq
FROM (
    SELECT UNNEST(STRING_TO_ARRAY(LOWER(title), ' ')) AS word
    FROM articles
) words
WHERE LENGTH(word) > 2
GROUP BY word
ORDER BY freq DESC;

-- Fast prefix search
SELECT keyword
FROM search_keywords
WHERE keyword LIKE 'data%'
ORDER BY frequency DESC
LIMIT 10;

When to Consider Elasticsearch

Your database search might not be enough when:

• You have millions of documents and need sub-100ms response times
• You need advanced features like faceted search, filters, aggregations
• You need fuzzy matching and typo tolerance
• You're searching across multiple data sources
• You need real-time updates at very high volume

For many applications though, database full-text search is perfectly adequate and much simpler to maintain.

Practical Example: Building a Blog Search

Let's build a complete search feature:

-- MySQL version
SELECT
    id,
    title,
    LEFT(body, 150) AS excerpt,
    author,
    created_at,
    MATCH(title, body) AGAINST('database tips' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('database tips' IN NATURAL LANGUAGE MODE)
  AND published = 1
ORDER BY relevance DESC
LIMIT 20;

-- PostgreSQL version
SELECT
    id,
    title,
    ts_headline('english', body, query, 'MaxWords=25') AS excerpt,
    author,
    created_at,
    ts_rank(search_vector, query) AS relevance
FROM articles, plainto_tsquery('english', 'database tips') query
WHERE search_vector @@ query
  AND published = true
ORDER BY relevance DESC
LIMIT 20;

Conclusion

Full-text search in SQL databases has come a long way. For many applications, it provides everything you need without the complexity of external search services.

MySQL is simpler to set up and works well for basic search needs.

PostgreSQL offers more power and flexibility, with better ranking and highlighting.

Start with what your database provides. You can always add Elasticsearch later if you outgrow it.

Want to Test Your Search Queries?

LunoDB makes it easy to write and test full-text search queries with instant results. See how your search performs before deploying to production. Download LunoDB and build better search features.