DEV Community

Cover image for Exploring MySQL Full-Text Search and Its Implementation in Laravel
Mahfuzur Rahman
Mahfuzur Rahman

Posted on

Exploring MySQL Full-Text Search and Its Implementation in Laravel

Imagine you’re building a news website or a blog, and you need a fast and efficient way to search through thousands (or even millions) of articles. Traditional LIKE queries in MySQL won’t cut it they’re slow and inefficient. This is where MySQL Full-Text Search (FTS) comes into play! 🚀

Full-Text Search is MySQL’s built-in solution for powerful, efficient text searching. In this article, we’ll break down what it is, how it works, and how you can implement it in Laravel.

What is MySQL Full-Text Search?

Full-Text Search (FTS) is a feature in MySQL that allows you to perform advanced text-based queries using indexes rather than scanning entire tables. This makes searches much faster compared to the traditional LIKE operator.

How It Works 🔍

  1. Indexing: MySQL creates a FULLTEXT index on specific columns.
  2. Tokenization: Text is broken into words (tokens) and stored in an index.
  3. Ranking: When a search query is executed, MySQL retrieves results based on relevance scoring.

Full-Text Search Modes

MySQL provides two main modes:

  1. Natural Language Mode (Default) Returns results ranked by relevance.
  2. Boolean Mode — Allows custom search logic using operators like +, -, OR, NOT.

Setting Up Full-Text Search in Laravel

1. Creating the Database Table
First, create a migration to add a FULLTEXT index to your table:

Schema::create('news', function (Blueprint $table) {
    $table->id();
    $table->string('title');
    $table->text('content');
    $table->timestamps();

    $table->fullText(['title', 'content']); // Adding FULLTEXT index
});
Enter fullscreen mode Exit fullscreen mode

2. Performing a Full-Text Search
You can now use MySQL’s MATCH() function in Laravel queries:

$search = 'election results';

$results = DB::table('news')
    ->whereRaw("MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)", [$search])
    ->get();
Enter fullscreen mode Exit fullscreen mode

This query will return articles ranked by relevance.

3. Using Boolean Mode for Advanced Searches

Need more control? Use Boolean Mode:

$search = '+election -fake';

$results = DB::table('news')
    ->whereRaw("MATCH(title, content) AGAINST(? IN BOOLEAN MODE)", [$search])
    ->get();
Enter fullscreen mode Exit fullscreen mode

This ensures results must contain “election” but must not contain “fake.”

4. Full-Text Search in Eloquent
Want a cleaner approach? Use Eloquent:

class News extends Model
{
    public function scopeSearch($query, $keywords)
    {
        return $query->whereRaw("MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)", [$keywords]);
    }
}

$articles = News::search('technology trends')->get();
Enter fullscreen mode Exit fullscreen mode

This makes it reusable across your application.

Optimizing Full-Text Search Performance

  1. Use Short Columns: Store only necessary text in the FULLTEXT index.
  2. Limit Results: Always paginate or limit query results.
  3. Consider External Search Engines: If your dataset is huge, try Meilisearch or Elasticsearch.
  4. MySQL Full-Text Search is a powerful tool for fast and efficient text searching. When combined with Laravel, it provides an easy and flexible way to implement advanced search features.

So, whether you’re building a news portal, a blog, or an e-commerce site, leveraging Full-Text Search can significantly enhance the user experience.

And with Redis Caching, it will be a lot faster. Happy Coding!!!!

Sentry image

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more