Efficient way to search single record from 6000k+ record DB in laravel

Issue

I want to know how to search a single record from a 6000k+ record database. I have written a query for it but I am facing 15s load time it’s too high for the efficient system so please help me to search in an efficient way to take lower response time in searching.

$users = DB::select('select * from tablefour where Provider_Business_Mailing_Address_Fax_Number = ?', array($request['id'])); 
return $users;

Solution

Make the column index then search will work fast on that column.This help me.

For Laravel:-
Do this in Schema

    final class AddIndexesTableFour extends Migration
{
    public function up(): void
    {
        Schema::table('tablefour', function (Blueprint $table) {
            $table->index('Provider_Business_Mailing_Address_Telephone_Number');
        });
    }

    public function down(): void
    {
        Schema::table('tablefour', function (Blueprint $table) {
            $table->dropIndex(['Provider_Business_Mailing_Address_Telephone_Number']);
        });
    }
}

Answered By – Rana Nadeem

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0