Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 “Using index condition”
My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different).
EXPLAIN SELECT
*
FROM
`automations`
WHERE
(`contact_id` = 22638
AND `job_class_name` = 'AppJobsMyJob'
AND `execute_at` = '2018-12-15 16:43:00')
AND `automations`.`deleted_at` IS NULL
LIMIT 1
Result of EXPLAIN:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|----|-------------|-------------|------------|------|--------------------------------|--------------------------------|---------|-------------------------|------|----------|-----------------------|
| 1 | SIMPLE | automations | NULL | ref | cId_job_executeAt_delAt_unique | cId_job_executeAt_delAt_unique | 780 | const,const,const,const | 1 | 100.00 | Using index condition |
I am inexperienced at optimizing MySql, but my guess would be that the Explain looks great, right?
Why on earth would queries like this be taking 350+ seconds? How can I diagnose and fix?
P.S. This is related to the E_WARNING: Error while sending STMT_PREPARE packet. PID=* error I frequently see.
CREATE TABLE `automations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`contact_id` int(10) unsigned NOT NULL,
`job_class_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`execute_at` datetime NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `cId_job_executeAt_delAt_unique` (`contact_id`,`job_class_name`,`execute_at`,`deleted_at`),
CONSTRAINT `automations_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1519 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
mysql index optimization mysql-5.7 slow-log
asked Dec 15 '18 at 19:28
RyanRyan
1315
|
show 2 more comments
My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different).
EXPLAIN SELECT
*
FROM
`automations`
WHERE
(`contact_id` = 22638
AND `job_class_name` = 'AppJobsMyJob'
AND `execute_at` = '2018-12-15 16:43:00')
AND `automations`.`deleted_at` IS NULL
LIMIT 1
Result of EXPLAIN:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|----|-------------|-------------|------------|------|--------------------------------|--------------------------------|---------|-------------------------|------|----------|-----------------------|
| 1 | SIMPLE | automations | NULL | ref | cId_job_executeAt_delAt_unique | cId_job_executeAt_delAt_unique | 780 | const,const,const,const | 1 | 100.00 | Using index condition |
I am inexperienced at optimizing MySql, but my guess would be that the Explain looks great, right?
Why on earth would queries like this be taking 350+ seconds? How can I diagnose and fix?
P.S. This is related to the E_WARNING: Error while sending STMT_PREPARE packet. PID=* error I frequently see.
CREATE TABLE `automations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`contact_id` int(10) unsigned NOT NULL,
`job_class_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`execute_at` datetime NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `cId_job_executeAt_delAt_unique` (`contact_id`,`job_class_name`,`execute_at`,`deleted_at`),
CONSTRAINT `automations_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1519 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
mysql index optimization mysql-5.7 slow-log
asked Dec 15 '18 at 19:28
RyanRyan
1315
1
Please add the. CREATE TABLE here, too.
– ypercubeᵀᴹ
Dec 15 '18 at 19:34
@ypercubeᵀᴹ I added the CREATE TABLE here now too.
– Ryan
Dec 15 '18 at 20:01
1
ThatCREATEis missing the definition ofcId_job_executeAt_delAt_unique!
– Rick James
Dec 15 '18 at 20:29
1
I am no expert, but have you tried doing it "step by step" first? Doing an Index-Only-Scan for example (selecting only the Id or Indexed attributes in that table). Maybe try to remove the SELECT * for now and select only attributes that you want to see. And check the performance difference? Maybe that will give you or some people here some hints
– Chessbrain
Dec 15 '18 at 20:43
@RickJames You were right that I'd accidentally pasted an outdated CREATE TABLE here, and now I've updated the question to have the correct one. My query is slow even with the 4-column composite index.
– Ryan
Dec 15 '18 at 21:03
|
show 2 more comments
My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different).
EXPLAIN SELECT
*
FROM
`automations`
WHERE
(`contact_id` = 22638
AND `job_class_name` = 'AppJobsMyJob'
AND `execute_at` = '2018-12-15 16:43:00')
AND `automations`.`deleted_at` IS NULL
LIMIT 1
Result of EXPLAIN:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|----|-------------|-------------|------------|------|--------------------------------|--------------------------------|---------|-------------------------|------|----------|-----------------------|
| 1 | SIMPLE | automations | NULL | ref | cId_job_executeAt_delAt_unique | cId_job_executeAt_delAt_unique | 780 | const,const,const,const | 1 | 100.00 | Using index condition |
I am inexperienced at optimizing MySql, but my guess would be that the Explain looks great, right?
Why on earth would queries like this be taking 350+ seconds? How can I diagnose and fix?
P.S. This is related to the E_WARNING: Error while sending STMT_PREPARE packet. PID=* error I frequently see.
CREATE TABLE `automations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`contact_id` int(10) unsigned NOT NULL,
`job_class_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`execute_at` datetime NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `cId_job_executeAt_delAt_unique` (`contact_id`,`job_class_name`,`execute_at`,`deleted_at`),
CONSTRAINT `automations_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1519 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
mysql index optimization mysql-5.7 slow-log
asked Dec 15 '18 at 19:28
RyanRyan
1315
My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different).
EXPLAIN SELECT
*
FROM
`automations`
WHERE
(`contact_id` = 22638
AND `job_class_name` = 'AppJobsMyJob'
AND `execute_at` = '2018-12-15 16:43:00')
AND `automations`.`deleted_at` IS NULL
LIMIT 1
Result of EXPLAIN:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|----|-------------|-------------|------------|------|--------------------------------|--------------------------------|---------|-------------------------|------|----------|-----------------------|
| 1 | SIMPLE | automations | NULL | ref | cId_job_executeAt_delAt_unique | cId_job_executeAt_delAt_unique | 780 | const,const,const,const | 1 | 100.00 | Using index condition |
I am inexperienced at optimizing MySql, but my guess would be that the Explain looks great, right?
Why on earth would queries like this be taking 350+ seconds? How can I diagnose and fix?
P.S. This is related to the E_WARNING: Error while sending STMT_PREPARE packet. PID=* error I frequently see.
CREATE TABLE `automations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`contact_id` int(10) unsigned NOT NULL,
`job_class_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`execute_at` datetime NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `cId_job_executeAt_delAt_unique` (`contact_id`,`job_class_name`,`execute_at`,`deleted_at`),
CONSTRAINT `automations_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1519 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
mysql index optimization mysql-5.7 slow-log
mysql index optimization mysql-5.7 slow-log
asked Dec 15 '18 at 19:28
RyanRyan
1315
asked Dec 15 '18 at 19:28
RyanRyan
1315
edited Dec 15 '18 at 21:01
Ryan
asked Dec 15 '18 at 19:28
RyanRyan
1315
asked Dec 15 '18 at 19:28
RyanRyan
1315
asked Dec 15 '18 at 19:28
RyanRyan
1315
1315
1
Please add the. CREATE TABLE here, too.
– ypercubeᵀᴹ
Dec 15 '18 at 19:34
@ypercubeᵀᴹ I added the CREATE TABLE here now too.
– Ryan
Dec 15 '18 at 20:01
1
ThatCREATEis missing the definition ofcId_job_executeAt_delAt_unique!
– Rick James
Dec 15 '18 at 20:29
1
I am no expert, but have you tried doing it "step by step" first? Doing an Index-Only-Scan for example (selecting only the Id or Indexed attributes in that table). Maybe try to remove the SELECT * for now and select only attributes that you want to see. And check the performance difference? Maybe that will give you or some people here some hints
– Chessbrain
Dec 15 '18 at 20:43
@RickJames You were right that I'd accidentally pasted an outdated CREATE TABLE here, and now I've updated the question to have the correct one. My query is slow even with the 4-column composite index.
– Ryan
Dec 15 '18 at 21:03
|
show 2 more comments
1
Please add the. CREATE TABLE here, too.
– ypercubeᵀᴹ
Dec 15 '18 at 19:34
@ypercubeᵀᴹ I added the CREATE TABLE here now too.
– Ryan
Dec 15 '18 at 20:01
1
ThatCREATEis missing the definition ofcId_job_executeAt_delAt_unique!
– Rick James
Dec 15 '18 at 20:29
1
I am no expert, but have you tried doing it "step by step" first? Doing an Index-Only-Scan for example (selecting only the Id or Indexed attributes in that table). Maybe try to remove the SELECT * for now and select only attributes that you want to see. And check the performance difference? Maybe that will give you or some people here some hints
– Chessbrain
Dec 15 '18 at 20:43
@RickJames You were right that I'd accidentally pasted an outdated CREATE TABLE here, and now I've updated the question to have the correct one. My query is slow even with the 4-column composite index.
– Ryan
Dec 15 '18 at 21:03
1
1
Please add the. CREATE TABLE here, too.
– ypercubeᵀᴹ
Dec 15 '18 at 19:34
Please add the. CREATE TABLE here, too.
– ypercubeᵀᴹ
Dec 15 '18 at 19:34
@ypercubeᵀᴹ I added the CREATE TABLE here now too.
– Ryan
Dec 15 '18 at 20:01
@ypercubeᵀᴹ I added the CREATE TABLE here now too.
– Ryan
Dec 15 '18 at 20:01
1
1
That
CREATE is missing the definition of cId_job_executeAt_delAt_unique!– Rick James
Dec 15 '18 at 20:29
That
CREATE is missing the definition of cId_job_executeAt_delAt_unique!– Rick James
Dec 15 '18 at 20:29
1
1
I am no expert, but have you tried doing it "step by step" first? Doing an Index-Only-Scan for example (selecting only the Id or Indexed attributes in that table). Maybe try to remove the SELECT * for now and select only attributes that you want to see. And check the performance difference? Maybe that will give you or some people here some hints
– Chessbrain
Dec 15 '18 at 20:43
I am no expert, but have you tried doing it "step by step" first? Doing an Index-Only-Scan for example (selecting only the Id or Indexed attributes in that table). Maybe try to remove the SELECT * for now and select only attributes that you want to see. And check the performance difference? Maybe that will give you or some people here some hints
– Chessbrain
Dec 15 '18 at 20:43
@RickJames You were right that I'd accidentally pasted an outdated CREATE TABLE here, and now I've updated the question to have the correct one. My query is slow even with the 4-column composite index.
– Ryan
Dec 15 '18 at 21:03
@RickJames You were right that I'd accidentally pasted an outdated CREATE TABLE here, and now I've updated the question to have the correct one. My query is slow even with the 4-column composite index.
– Ryan
Dec 15 '18 at 21:03
|
show 2 more comments
2 Answers
2
active
oldest
votes
The optimal index for this query would be a composite index with 4 columns, in any order:
INDEX(contact_id, job_class_name, execute_at, deleted_at)
And, depending on the order you pick, you can get rid of one of the existing KEYs.
Watch out for backslashes. 'AppJobsMyJob' may be treated as App[LF]obs[CR]yJob or AppJobsMyJob.
352 seconds is really unreasonable. Perhaps something else was going on, and blocking access to the rows and/or the table?
"Using index condition" is not the same as "Using index". The latter indicates a "covering index", which is not practical for this query and table.
answered Dec 15 '18 at 20:31
Rick JamesRick James
41.8k22258
I updated my question to show that I'm already using a 4-column composite index, so that isn't the root cause of the slowness. I don't think the backslashes are problematic; the query often works fine. Is there anything else you could elaborate on for your last 2 paragraphs or any other ideas? Thanks.
– Ryan
Dec 15 '18 at 21:06
(I think Laravel is appropriately handling the backslashes via parameterized query.)
– Ryan
Dec 15 '18 at 21:18
1
@Ryan - Turn on the slowlog in hopes of catching something useful. If you can spot the query taking a long time, doSHOW FULL PROCESSLIST;(as root) to see if you can spot some other take doing something. (I'm running out of ideas.)
– Rick James
Dec 16 '18 at 0:25
Interesting. E.g. getting alerts from stackoverflow.com/a/33253451/470749. Thanks.
– Ryan
Dec 16 '18 at 1:46
add a comment |
My queries that I thought took 352 seconds really only took 0.352 seconds! 🤦♂️
https://laravel.io/forum/03-04-2014-what-time-format-does-dbgetquerylog-return showed me that Laravel's DB::getQueryLog() shows "time" as milliseconds (microseconds multiplied by 1000), not seconds.
What an embarrassing mistake (poor assumption). So I need to edit my 500-point bounty question: https://stackoverflow.com/questions/53469793/e-warning-error-while-sending-stmt-prepare-packet-pid/54374937?noredirect=1#comment95579631_53469793
answered 12 mins ago
RyanRyan
1315
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f225068%2fsimple-indexed-select-query-takes-350-seconds-even-though-filtered-100-00%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The optimal index for this query would be a composite index with 4 columns, in any order:
INDEX(contact_id, job_class_name, execute_at, deleted_at)
And, depending on the order you pick, you can get rid of one of the existing KEYs.
Watch out for backslashes. 'AppJobsMyJob' may be treated as App[LF]obs[CR]yJob or AppJobsMyJob.
352 seconds is really unreasonable. Perhaps something else was going on, and blocking access to the rows and/or the table?
"Using index condition" is not the same as "Using index". The latter indicates a "covering index", which is not practical for this query and table.
answered Dec 15 '18 at 20:31
Rick JamesRick James
41.8k22258
I updated my question to show that I'm already using a 4-column composite index, so that isn't the root cause of the slowness. I don't think the backslashes are problematic; the query often works fine. Is there anything else you could elaborate on for your last 2 paragraphs or any other ideas? Thanks.
– Ryan
Dec 15 '18 at 21:06
(I think Laravel is appropriately handling the backslashes via parameterized query.)
– Ryan
Dec 15 '18 at 21:18
1
@Ryan - Turn on the slowlog in hopes of catching something useful. If you can spot the query taking a long time, doSHOW FULL PROCESSLIST;(as root) to see if you can spot some other take doing something. (I'm running out of ideas.)
– Rick James
Dec 16 '18 at 0:25
Interesting. E.g. getting alerts from stackoverflow.com/a/33253451/470749. Thanks.
– Ryan
Dec 16 '18 at 1:46
add a comment |
The optimal index for this query would be a composite index with 4 columns, in any order:
INDEX(contact_id, job_class_name, execute_at, deleted_at)
And, depending on the order you pick, you can get rid of one of the existing KEYs.
Watch out for backslashes. 'AppJobsMyJob' may be treated as App[LF]obs[CR]yJob or AppJobsMyJob.
352 seconds is really unreasonable. Perhaps something else was going on, and blocking access to the rows and/or the table?
"Using index condition" is not the same as "Using index". The latter indicates a "covering index", which is not practical for this query and table.
answered Dec 15 '18 at 20:31
Rick JamesRick James
41.8k22258
I updated my question to show that I'm already using a 4-column composite index, so that isn't the root cause of the slowness. I don't think the backslashes are problematic; the query often works fine. Is there anything else you could elaborate on for your last 2 paragraphs or any other ideas? Thanks.
– Ryan
Dec 15 '18 at 21:06
(I think Laravel is appropriately handling the backslashes via parameterized query.)
– Ryan
Dec 15 '18 at 21:18
1
@Ryan - Turn on the slowlog in hopes of catching something useful. If you can spot the query taking a long time, doSHOW FULL PROCESSLIST;(as root) to see if you can spot some other take doing something. (I'm running out of ideas.)
– Rick James
Dec 16 '18 at 0:25
Interesting. E.g. getting alerts from stackoverflow.com/a/33253451/470749. Thanks.
– Ryan
Dec 16 '18 at 1:46
add a comment |
The optimal index for this query would be a composite index with 4 columns, in any order:
INDEX(contact_id, job_class_name, execute_at, deleted_at)
And, depending on the order you pick, you can get rid of one of the existing KEYs.
Watch out for backslashes. 'AppJobsMyJob' may be treated as App[LF]obs[CR]yJob or AppJobsMyJob.
352 seconds is really unreasonable. Perhaps something else was going on, and blocking access to the rows and/or the table?
"Using index condition" is not the same as "Using index". The latter indicates a "covering index", which is not practical for this query and table.
answered Dec 15 '18 at 20:31
Rick JamesRick James
41.8k22258
The optimal index for this query would be a composite index with 4 columns, in any order:
INDEX(contact_id, job_class_name, execute_at, deleted_at)
And, depending on the order you pick, you can get rid of one of the existing KEYs.
Watch out for backslashes. 'AppJobsMyJob' may be treated as App[LF]obs[CR]yJob or AppJobsMyJob.
352 seconds is really unreasonable. Perhaps something else was going on, and blocking access to the rows and/or the table?
"Using index condition" is not the same as "Using index". The latter indicates a "covering index", which is not practical for this query and table.
answered Dec 15 '18 at 20:31
Rick JamesRick James
41.8k22258
edited Dec 15 '18 at 20:38
answered Dec 15 '18 at 20:31
Rick JamesRick James
41.8k22258
answered Dec 15 '18 at 20:31
Rick JamesRick James
41.8k22258
answered Dec 15 '18 at 20:31
Rick JamesRick James
41.8k22258
41.8k22258
I updated my question to show that I'm already using a 4-column composite index, so that isn't the root cause of the slowness. I don't think the backslashes are problematic; the query often works fine. Is there anything else you could elaborate on for your last 2 paragraphs or any other ideas? Thanks.
– Ryan
Dec 15 '18 at 21:06
(I think Laravel is appropriately handling the backslashes via parameterized query.)
– Ryan
Dec 15 '18 at 21:18
1
@Ryan - Turn on the slowlog in hopes of catching something useful. If you can spot the query taking a long time, doSHOW FULL PROCESSLIST;(as root) to see if you can spot some other take doing something. (I'm running out of ideas.)
– Rick James
Dec 16 '18 at 0:25
Interesting. E.g. getting alerts from stackoverflow.com/a/33253451/470749. Thanks.
– Ryan
Dec 16 '18 at 1:46
add a comment |
I updated my question to show that I'm already using a 4-column composite index, so that isn't the root cause of the slowness. I don't think the backslashes are problematic; the query often works fine. Is there anything else you could elaborate on for your last 2 paragraphs or any other ideas? Thanks.
– Ryan
Dec 15 '18 at 21:06
(I think Laravel is appropriately handling the backslashes via parameterized query.)
– Ryan
Dec 15 '18 at 21:18
1
@Ryan - Turn on the slowlog in hopes of catching something useful. If you can spot the query taking a long time, doSHOW FULL PROCESSLIST;(as root) to see if you can spot some other take doing something. (I'm running out of ideas.)
– Rick James
Dec 16 '18 at 0:25
Interesting. E.g. getting alerts from stackoverflow.com/a/33253451/470749. Thanks.
– Ryan
Dec 16 '18 at 1:46
I updated my question to show that I'm already using a 4-column composite index, so that isn't the root cause of the slowness. I don't think the backslashes are problematic; the query often works fine. Is there anything else you could elaborate on for your last 2 paragraphs or any other ideas? Thanks.
– Ryan
Dec 15 '18 at 21:06
I updated my question to show that I'm already using a 4-column composite index, so that isn't the root cause of the slowness. I don't think the backslashes are problematic; the query often works fine. Is there anything else you could elaborate on for your last 2 paragraphs or any other ideas? Thanks.
– Ryan
Dec 15 '18 at 21:06
(I think Laravel is appropriately handling the backslashes via parameterized query.)
– Ryan
Dec 15 '18 at 21:18
(I think Laravel is appropriately handling the backslashes via parameterized query.)
– Ryan
Dec 15 '18 at 21:18
1
1
@Ryan - Turn on the slowlog in hopes of catching something useful. If you can spot the query taking a long time, do
SHOW FULL PROCESSLIST; (as root) to see if you can spot some other take doing something. (I'm running out of ideas.)– Rick James
Dec 16 '18 at 0:25
@Ryan - Turn on the slowlog in hopes of catching something useful. If you can spot the query taking a long time, do
SHOW FULL PROCESSLIST; (as root) to see if you can spot some other take doing something. (I'm running out of ideas.)– Rick James
Dec 16 '18 at 0:25
Interesting. E.g. getting alerts from stackoverflow.com/a/33253451/470749. Thanks.
– Ryan
Dec 16 '18 at 1:46
Interesting. E.g. getting alerts from stackoverflow.com/a/33253451/470749. Thanks.
– Ryan
Dec 16 '18 at 1:46
add a comment |
My queries that I thought took 352 seconds really only took 0.352 seconds! 🤦♂️
https://laravel.io/forum/03-04-2014-what-time-format-does-dbgetquerylog-return showed me that Laravel's DB::getQueryLog() shows "time" as milliseconds (microseconds multiplied by 1000), not seconds.
What an embarrassing mistake (poor assumption). So I need to edit my 500-point bounty question: https://stackoverflow.com/questions/53469793/e-warning-error-while-sending-stmt-prepare-packet-pid/54374937?noredirect=1#comment95579631_53469793
answered 12 mins ago
RyanRyan
1315
add a comment |
My queries that I thought took 352 seconds really only took 0.352 seconds! 🤦♂️
https://laravel.io/forum/03-04-2014-what-time-format-does-dbgetquerylog-return showed me that Laravel's DB::getQueryLog() shows "time" as milliseconds (microseconds multiplied by 1000), not seconds.
What an embarrassing mistake (poor assumption). So I need to edit my 500-point bounty question: https://stackoverflow.com/questions/53469793/e-warning-error-while-sending-stmt-prepare-packet-pid/54374937?noredirect=1#comment95579631_53469793
answered 12 mins ago
RyanRyan
1315
add a comment |
My queries that I thought took 352 seconds really only took 0.352 seconds! 🤦♂️
https://laravel.io/forum/03-04-2014-what-time-format-does-dbgetquerylog-return showed me that Laravel's DB::getQueryLog() shows "time" as milliseconds (microseconds multiplied by 1000), not seconds.
What an embarrassing mistake (poor assumption). So I need to edit my 500-point bounty question: https://stackoverflow.com/questions/53469793/e-warning-error-while-sending-stmt-prepare-packet-pid/54374937?noredirect=1#comment95579631_53469793
answered 12 mins ago
RyanRyan
1315
My queries that I thought took 352 seconds really only took 0.352 seconds! 🤦♂️
https://laravel.io/forum/03-04-2014-what-time-format-does-dbgetquerylog-return showed me that Laravel's DB::getQueryLog() shows "time" as milliseconds (microseconds multiplied by 1000), not seconds.
What an embarrassing mistake (poor assumption). So I need to edit my 500-point bounty question: https://stackoverflow.com/questions/53469793/e-warning-error-while-sending-stmt-prepare-packet-pid/54374937?noredirect=1#comment95579631_53469793
answered 12 mins ago
RyanRyan
1315
answered 12 mins ago
RyanRyan
1315
answered 12 mins ago
RyanRyan
1315
answered 12 mins ago
RyanRyan
1315
1315
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f225068%2fsimple-indexed-select-query-takes-350-seconds-even-though-filtered-100-00%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
1
Please add the. CREATE TABLE here, too.
– ypercubeᵀᴹ
Dec 15 '18 at 19:34
@ypercubeᵀᴹ I added the CREATE TABLE here now too.
– Ryan
Dec 15 '18 at 20:01
1
That
CREATEis missing the definition ofcId_job_executeAt_delAt_unique!– Rick James
Dec 15 '18 at 20:29
1
I am no expert, but have you tried doing it "step by step" first? Doing an Index-Only-Scan for example (selecting only the Id or Indexed attributes in that table). Maybe try to remove the SELECT * for now and select only attributes that you want to see. And check the performance difference? Maybe that will give you or some people here some hints
– Chessbrain
Dec 15 '18 at 20:43
@RickJames You were right that I'd accidentally pasted an outdated CREATE TABLE here, and now I've updated the question to have the correct one. My query is slow even with the 4-column composite index.
– Ryan
Dec 15 '18 at 21:03