Query too slow when operating with 1 million data on table

@jinsonjose if you use eloquent and have a relationship with your User hasMany UserProfile set up then you can with

$user = User::where('users.type', 'member')->with('userProfiles')->whereHas(
    'userProfiles', function ($query) {
        $query->where('profile_status', 'approved');
    }
)->select('users.name', 'users.email',
    'users.location', 'users.type', 'users.ip', 'users.last_login', 'users.created_at', 'users.id',
    'user_profiles.profile_status')->skip(($page - 1) * $this->perPage)->limit($this->perPage)->get();

not sure what

when operating with 10 lakhs data

means though. Can you explain?

10 Lacs means 1 million @automica

@gitwithravish sure. hes passing a limit in, so not sure why the number of rows has an affect here.

if eager loading, with 'with' doesn't help, then using chunk will

https://laravel.com/docs/8.x/queries#chunking-results

@jinsonjose be sure all columns used for joining and filtering (users.type, user_profiles.user_id and user_profile.profile_status) are indexes

@automica in user table have 10 lakhs data its take around 15 seconds for that query to result

Do you have a problem to write that you have a million rows in the table? Lakhs uses in your country not here...

@michaloravec yes i am facing the millions rows data issue in table query take much time for load that data!

Can you do ->toSql() instead of ->get() and run the query manually with the word "EXPLAIN " in front of it?

@automica

$user = User::where('users.type', 'member')->with('userProfiles')->whereHas(
    'userProfiles', function ($query) {
        $query->where('profile_status', 'approved');
    }
)->select('users.name', 'users.email',
    'users.location', 'users.type', 'users.ip', 'users.last_login', 'users.created_at', 'users.id',
    'user_profiles.profile_status')->skip(($page - 1) * $this->perPage)->limit($this->perPage)->get();

this query returns error:

    "message": "Call to undefined method App\User::userProfiles()",
    "exception": "BadMethodCallException",

@jinsonjose that’s throwing an error as you don’t have that eloquent relationship set up in your User model.

User hasMany UserProfile

// User model 

public function userProfiles() {

return $this->hasMany(UserProfile::class);
}

thanks @automica now its loading faster