How to reduce loading time of sql query?
![Multi tool use Multi tool use](http://sgv.ssvwv.com/sg/ssvwvcomimagb.png)
Multi tool use
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:
- role table index
permission table index
model has role table
role has permission table
mysql performance laravel-5 eloquent
add a comment |
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:
- role table index
permission table index
model has role table
role has permission table
mysql performance laravel-5 eloquent
Please provide the generatedSELECT
statement andSHOW CREATE TABLE
for each table.
– Rick James
Jan 1 at 2:04
add a comment |
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:
- role table index
permission table index
model has role table
role has permission table
mysql performance laravel-5 eloquent
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:
- role table index
permission table index
model has role table
role has permission table
mysql performance laravel-5 eloquent
mysql performance laravel-5 eloquent
edited Jan 16 at 0:20
joun
asked Dec 31 '18 at 7:12
jounjoun
87114
87114
Please provide the generatedSELECT
statement andSHOW CREATE TABLE
for each table.
– Rick James
Jan 1 at 2:04
add a comment |
Please provide the generatedSELECT
statement andSHOW CREATE TABLE
for each table.
– Rick James
Jan 1 at 2:04
Please provide the generated
SELECT
statement and SHOW CREATE TABLE
for each table.– Rick James
Jan 1 at 2:04
Please provide the generated
SELECT
statement and SHOW CREATE TABLE
for each table.– Rick James
Jan 1 at 2:04
add a comment |
1 Answer
1
active
oldest
votes
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 ...
I load it and display it using datatable in blade.. I already try pagination and decrease load but it is not compatible for datatable ..
– joun
Dec 31 '18 at 7:28
What do you mean with proper index?
– joun
Dec 31 '18 at 7:30
be sure you have the index on your table .. and expecially a composite index on the table itemregistrations that use the 3 columns mentioned
– scaisEdge
Dec 31 '18 at 7:50
I have updated my question with indexing view..is it right?
– joun
Dec 31 '18 at 8:10
you have each column in a separated index .. this is pretty unuseful for you goal .. you should use an index that involve the 3 column you use in ON clause .. writing the cols name left to right based on the high cardinality .. i have updated the answer
– scaisEdge
Dec 31 '18 at 8:25
|
show 13 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53984640%2fhow-to-reduce-loading-time-of-sql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 ...
I load it and display it using datatable in blade.. I already try pagination and decrease load but it is not compatible for datatable ..
– joun
Dec 31 '18 at 7:28
What do you mean with proper index?
– joun
Dec 31 '18 at 7:30
be sure you have the index on your table .. and expecially a composite index on the table itemregistrations that use the 3 columns mentioned
– scaisEdge
Dec 31 '18 at 7:50
I have updated my question with indexing view..is it right?
– joun
Dec 31 '18 at 8:10
you have each column in a separated index .. this is pretty unuseful for you goal .. you should use an index that involve the 3 column you use in ON clause .. writing the cols name left to right based on the high cardinality .. i have updated the answer
– scaisEdge
Dec 31 '18 at 8:25
|
show 13 more comments
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 ...
I load it and display it using datatable in blade.. I already try pagination and decrease load but it is not compatible for datatable ..
– joun
Dec 31 '18 at 7:28
What do you mean with proper index?
– joun
Dec 31 '18 at 7:30
be sure you have the index on your table .. and expecially a composite index on the table itemregistrations that use the 3 columns mentioned
– scaisEdge
Dec 31 '18 at 7:50
I have updated my question with indexing view..is it right?
– joun
Dec 31 '18 at 8:10
you have each column in a separated index .. this is pretty unuseful for you goal .. you should use an index that involve the 3 column you use in ON clause .. writing the cols name left to right based on the high cardinality .. i have updated the answer
– scaisEdge
Dec 31 '18 at 8:25
|
show 13 more comments
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 ...
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 ...
edited Dec 31 '18 at 8:25
answered Dec 31 '18 at 7:25
scaisEdgescaisEdge
93.3k104970
93.3k104970
I load it and display it using datatable in blade.. I already try pagination and decrease load but it is not compatible for datatable ..
– joun
Dec 31 '18 at 7:28
What do you mean with proper index?
– joun
Dec 31 '18 at 7:30
be sure you have the index on your table .. and expecially a composite index on the table itemregistrations that use the 3 columns mentioned
– scaisEdge
Dec 31 '18 at 7:50
I have updated my question with indexing view..is it right?
– joun
Dec 31 '18 at 8:10
you have each column in a separated index .. this is pretty unuseful for you goal .. you should use an index that involve the 3 column you use in ON clause .. writing the cols name left to right based on the high cardinality .. i have updated the answer
– scaisEdge
Dec 31 '18 at 8:25
|
show 13 more comments
I load it and display it using datatable in blade.. I already try pagination and decrease load but it is not compatible for datatable ..
– joun
Dec 31 '18 at 7:28
What do you mean with proper index?
– joun
Dec 31 '18 at 7:30
be sure you have the index on your table .. and expecially a composite index on the table itemregistrations that use the 3 columns mentioned
– scaisEdge
Dec 31 '18 at 7:50
I have updated my question with indexing view..is it right?
– joun
Dec 31 '18 at 8:10
you have each column in a separated index .. this is pretty unuseful for you goal .. you should use an index that involve the 3 column you use in ON clause .. writing the cols name left to right based on the high cardinality .. i have updated the answer
– scaisEdge
Dec 31 '18 at 8:25
I load it and display it using datatable in blade.. I already try pagination and decrease load but it is not compatible for datatable ..
– joun
Dec 31 '18 at 7:28
I load it and display it using datatable in blade.. I already try pagination and decrease load but it is not compatible for datatable ..
– joun
Dec 31 '18 at 7:28
What do you mean with proper index?
– joun
Dec 31 '18 at 7:30
What do you mean with proper index?
– joun
Dec 31 '18 at 7:30
be sure you have the index on your table .. and expecially a composite index on the table itemregistrations that use the 3 columns mentioned
– scaisEdge
Dec 31 '18 at 7:50
be sure you have the index on your table .. and expecially a composite index on the table itemregistrations that use the 3 columns mentioned
– scaisEdge
Dec 31 '18 at 7:50
I have updated my question with indexing view..is it right?
– joun
Dec 31 '18 at 8:10
I have updated my question with indexing view..is it right?
– joun
Dec 31 '18 at 8:10
you have each column in a separated index .. this is pretty unuseful for you goal .. you should use an index that involve the 3 column you use in ON clause .. writing the cols name left to right based on the high cardinality .. i have updated the answer
– scaisEdge
Dec 31 '18 at 8:25
you have each column in a separated index .. this is pretty unuseful for you goal .. you should use an index that involve the 3 column you use in ON clause .. writing the cols name left to right based on the high cardinality .. i have updated the answer
– scaisEdge
Dec 31 '18 at 8:25
|
show 13 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53984640%2fhow-to-reduce-loading-time-of-sql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
UAw9EV8juFaRaaAt JuodU8OxUfJ94jJXz9zrXC9g084
Please provide the generated
SELECT
statement andSHOW CREATE TABLE
for each table.– Rick James
Jan 1 at 2:04