Bdtjtk

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.

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` = 'AppUser'



  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` = 'AppUser'



  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

2. permission table index
   




3. model has role table
   




4. role has permission table

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 ...