CODEWITHSUNDEEP
mysqlsqllaravel
hassan raza
hassan raza

Reputation: 196

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

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;

Upvotes: 4

Views: 595

Answers (2)

Dilip Hirapara
Dilip Hirapara

Reputation: 15296

1st Only select those columns which you needed.

$users = \DB::table('tablefour')->select(['columnone','columntwo'])->whereIn('Provider_Business_Mailing_Address_Fax_Number',array($request['id']))->get();
return $users;

2nd

Make indexing on those columns in which columns are you selecting.

Check @Rana Nadeem answer for how to make indexing.

using migration

Schema::table('tablefour', function (Blueprint $table) {
    $table->index('user_id');
});

Upvotes: 2

Rana Nadeem
Rana Nadeem

Reputation: 1225

enter image description here

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']);
        });
    }
}

Upvotes: 5

Related Questions