Full Text Search in Laravel and MySQL 5

This tutorial will show you how to implement Full-Text search capability in Laravel 7 MySQL v5.7.26. Although Laravel currently doesn’t support FULLTEXT indexes, there’s an easy workaround to implement this.

Points to note about Full Text Search

MySQL has support for full-text indexing and searching:

• A full-text index in MySQL is an index of type FULLTEXT.
• Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.
• A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created or added later using ALTER TABLE or CREATE INDEX.
• For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

Adding Alter Table Statement for FullText Index in Our Migration File

Adding a full-text index in the migration file is simple. Below, there’s a simple post table with an up() function where we have to add the DB::statement() to alter the table. Don’t forget to import the DB facade on the top.

We also have to change the down() function. This will drop the index when we roll back our migration or drop tables.

use Illuminate\Support\Facades\DB;
///....
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            ///....
        });
        // Because Laravel doesn't support full text search migration
        DB::statement('ALTER TABLE `posts` ADD FULLTEXT INDEX post_title_index (title)');
    }
/**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('posts', function($table) {
	    $table->dropIndex('post_title_index');
	});
        Schema::dropIfExists('properties');
    }

Executing Full Text Search in the Controller

Here’s a sample search() function in a typical laravel controller where we search for posts using the full-text index that we just created in our table.

Here, assuming that the request has a parameter query, we can search posts using the simple lines of code below:

public function search(Request $request)
{
	$q = $request->input('query');
	 
	$posts = Post::whereRaw(
	        "MATCH(title) AGAINST(?)", 
	        array($q)
	)->get();
	 
	return view('posts.index', compact('posts'));
}

Okay, that’s it. So, that’s how easy it is to implement a full-text search in a laravel project.