CODEWITHSUNDEEP
mysqlperformancelaravel-5eloquent
joun
joun

Reputation: 664

How to reduce loading time of sql query?

I had created a query to get a list of staffs using this query. It is run after checked the permission level of the login user.

  if (Auth::user()->hasPermissionTo('All Sections')) {

  $itemregistrations = DB::table('itemregistrations')
                       ->join('sections', 'itemregistrations.sectionid', '=', 'sections.sectionid')
                       ->join('categories', 'itemregistrations.categoryid', '=', 'categories.categoryid')
                       ->join('operasi', 'itemregistrations.operasiid', '=', 'operasi.operasiid')
                       ->select('itemregistrations.ItemRegistrationID','itemregistrations.name', 'itemregistrations.Nobadan', 'sections.sectionname', 'categories.categoryname', 'operasi.operasiname')
                       ->get();

}

However, the query gets loading quite long, about a minute to finish loading. The list displayed about 1115.

How to reduce the loading time?

I read about eager loading to decrease the loading time. But my trial not success.

section is the department of staffs.

categories is the staff level

operasi is the grade of staff, related to categories, each category has its own operasiname.

This is the indexing on itemregistrations table.

enter image description here

I had installed laravel debugger and produce this result: -6 views -4 queries

  select * from `users` where `id` = 1 limit 1

  select `permissions`.*, `model_has_permissions`.`model_id` as `pivot_model_id`, `model_has_permissions`.`permission_id` as 
  `pivot_permission_id` from `permissions` 
  inner join `model_has_permissions` on `permissions`.`id` = `model_has_permissions`.`permission_id` 
  where `model_has_permissions`.`model_id` = 1 and `model_has_permissions`.`model_type` = 'App\User'

  select `roles`.*, `model_has_roles`.`model_id` as `pivot_model_id`, `model_has_roles`.`role_id` as `pivot_role_id` from `roles` 
  inner join `model_has_roles` on `roles`.`id` = `model_has_roles`.`role_id` 
  where `model_has_roles`.`model_id` = 1 and `model_has_roles`.`model_type` = 'App\User'

  select `itemregistrations`.`ItemRegistrationID`, `itemregistrations`.`name`, 
   `itemregistrations`.`Nobadan`, `sections`.`sectionname`, `categories`.`categoryname`, `operasi`.`operasiname` 
   from `itemregistrations` inner join `sections` on `itemregistrations`.`sectionid` = `sections`.`sectionid` 
   inner join `categories` on `itemregistrations`.`categoryid` = `categories`.`categoryid` 
   inner join `operasi` on `itemregistrations`.`operasiid` = `operasi`.`operasiid`

-1116 gates

The above query is filtered according to few permissions.

these are indexes for table involved:

  1. role table index

role index

  1. permission table index permission

  2. model has role table enter image description here

  3. role has permission table

enter image description here

Upvotes: 0

Views: 1371

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

for the DB side be sure you have proper index on the columns involved in JOIN 

sections sectionid
categories categoryid
operasi operasiid

expecially a composite index on 

itemregistrations   (sectionid , categoryid , operasiid )

anyway the load of 1256 si pretty unuseful in real app ..
for this you could reduce the loading time for the data show using pagination

based on you cardinality you should build an index

itemregistrations (operasiid, sectionid , categoryid )

could be you need remove the index on the same columns involved and leave only the composite ...

Upvotes: 1

Related Questions