Need help improving query performance
I have this simplified version of my database:
- http://sqlfiddle.com/#!9/4b903/2/0
I have a million rows in history and my problem is the query is so slow. It would take more or less 2 minutes.
I'm guessing GROUP BY t1.table1_id makes the query slow. I think I need to make a composite index but I'm not sure how to do it.
Update:
This is what I need to achieve in the query:
- i want to get the records that meet a certain criteria, particularly that the created_at field is within the last 24 hours
- i need to get the record that immediately precedes the records in #1
- further filter results in #1 to records whose price column has had a different value in a record's history, other than -1
mysql query-performance
asked Apr 25 '16 at 8:28
kmliguekmligue
312
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have this simplified version of my database:
- http://sqlfiddle.com/#!9/4b903/2/0
I have a million rows in history and my problem is the query is so slow. It would take more or less 2 minutes.
I'm guessing GROUP BY t1.table1_id makes the query slow. I think I need to make a composite index but I'm not sure how to do it.
Update:
This is what I need to achieve in the query:
- i want to get the records that meet a certain criteria, particularly that the created_at field is within the last 24 hours
- i need to get the record that immediately precedes the records in #1
- further filter results in #1 to records whose price column has had a different value in a record's history, other than -1
mysql query-performance
asked Apr 25 '16 at 8:28
kmliguekmligue
312
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
2
Edit your question with details of the real create table statements, and the plan for the actual query
– Philᵀᴹ
Apr 25 '16 at 9:14
2
Your sqlfiddle example contains no secondary indexes so we need to see the real structure (even if you might want to anonymise some column names). Use EXPLAIN to get the query plan.
– jkavalik
Apr 25 '16 at 10:12
In #2, do you want "the record that immediately precedes the oldest of the records in #1"?
– Rick James
Apr 26 '16 at 21:33
@RickJames Yes.
– kmligue
Apr 27 '16 at 3:46
add a comment |
I have this simplified version of my database:
- http://sqlfiddle.com/#!9/4b903/2/0
I have a million rows in history and my problem is the query is so slow. It would take more or less 2 minutes.
I'm guessing GROUP BY t1.table1_id makes the query slow. I think I need to make a composite index but I'm not sure how to do it.
Update:
This is what I need to achieve in the query:
- i want to get the records that meet a certain criteria, particularly that the created_at field is within the last 24 hours
- i need to get the record that immediately precedes the records in #1
- further filter results in #1 to records whose price column has had a different value in a record's history, other than -1
mysql query-performance
asked Apr 25 '16 at 8:28
kmliguekmligue
312
I have this simplified version of my database:
- http://sqlfiddle.com/#!9/4b903/2/0
I have a million rows in history and my problem is the query is so slow. It would take more or less 2 minutes.
I'm guessing GROUP BY t1.table1_id makes the query slow. I think I need to make a composite index but I'm not sure how to do it.
Update:
This is what I need to achieve in the query:
- i want to get the records that meet a certain criteria, particularly that the created_at field is within the last 24 hours
- i need to get the record that immediately precedes the records in #1
- further filter results in #1 to records whose price column has had a different value in a record's history, other than -1
mysql query-performance
mysql query-performance
asked Apr 25 '16 at 8:28
kmliguekmligue
312
asked Apr 25 '16 at 8:28
kmliguekmligue
312
edited Apr 26 '16 at 2:09
kmligue
asked Apr 25 '16 at 8:28
kmliguekmligue
312
asked Apr 25 '16 at 8:28
kmliguekmligue
312
asked Apr 25 '16 at 8:28
kmliguekmligue
312
312
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
2
Edit your question with details of the real create table statements, and the plan for the actual query
– Philᵀᴹ
Apr 25 '16 at 9:14
2
Your sqlfiddle example contains no secondary indexes so we need to see the real structure (even if you might want to anonymise some column names). Use EXPLAIN to get the query plan.
– jkavalik
Apr 25 '16 at 10:12
In #2, do you want "the record that immediately precedes the oldest of the records in #1"?
– Rick James
Apr 26 '16 at 21:33
@RickJames Yes.
– kmligue
Apr 27 '16 at 3:46
add a comment |
2
Edit your question with details of the real create table statements, and the plan for the actual query
– Philᵀᴹ
Apr 25 '16 at 9:14
2
Your sqlfiddle example contains no secondary indexes so we need to see the real structure (even if you might want to anonymise some column names). Use EXPLAIN to get the query plan.
– jkavalik
Apr 25 '16 at 10:12
In #2, do you want "the record that immediately precedes the oldest of the records in #1"?
– Rick James
Apr 26 '16 at 21:33
@RickJames Yes.
– kmligue
Apr 27 '16 at 3:46
2
2
Edit your question with details of the real create table statements, and the plan for the actual query
– Philᵀᴹ
Apr 25 '16 at 9:14
Edit your question with details of the real create table statements, and the plan for the actual query
– Philᵀᴹ
Apr 25 '16 at 9:14
2
2
Your sqlfiddle example contains no secondary indexes so we need to see the real structure (even if you might want to anonymise some column names). Use EXPLAIN to get the query plan.
– jkavalik
Apr 25 '16 at 10:12
Your sqlfiddle example contains no secondary indexes so we need to see the real structure (even if you might want to anonymise some column names). Use EXPLAIN to get the query plan.
– jkavalik
Apr 25 '16 at 10:12
In #2, do you want "the record that immediately precedes the oldest of the records in #1"?
– Rick James
Apr 26 '16 at 21:33
In #2, do you want "the record that immediately precedes the oldest of the records in #1"?
– Rick James
Apr 26 '16 at 21:33
@RickJames Yes.
– kmligue
Apr 27 '16 at 3:46
@RickJames Yes.
– kmligue
Apr 27 '16 at 3:46
add a comment |
1 Answer
1
active
oldest
votes
history: INDEX(created_reason, created_at)
Are you trying to avoid the "last record" via the price <>? If something else, then consider changing AND price <> ( SELECT ... LIMIT 1 ) to AND NOT EXISTS ( SELECT * ... )
Please explain, in words, what the query is trying to do. Perhaps some drastic reformulation is called for.
Anyway, it is a bad query... Don't GROUP BY one thing, but fetch several non-aggregate things. You will get unpredictable values for everything but table1_id.
More
It is unclear why you have two tables. The 3 steps are unclear. But here is a guess:
SELECT ch.*
FROM ( SELECT id,
price
FROM ( SELECT min(id) AS id
FROM history AS ah
WHERE created_at > NOW() - INTERVAL 24 HOUR
AND price != -1
) AS yesterday
JOIN history bh
WHERE bh.id < a.yesterday.id
AND bh.price != -1
ORDER BY bh.id DESC
LIMIT 1
) AS previous
JOIN history AS ch
WHERE ch.price != previous.price
AND ch.price != -1
AND ch.created_at > NOW() - INTERVAL 24 HOUR;
INDEX(created_at, id)
The innermost SELECT is item #1, etc.
Even if it is not the 'right' solution, maybe it will give you some clues.
answered Apr 25 '16 at 22:06
Rick JamesRick James
43.7k22259
Trying again...
– Rick James
Apr 27 '16 at 4:25
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%2f136425%2fneed-help-improving-query-performance%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
history: INDEX(created_reason, created_at)
Are you trying to avoid the "last record" via the price <>? If something else, then consider changing AND price <> ( SELECT ... LIMIT 1 ) to AND NOT EXISTS ( SELECT * ... )
Please explain, in words, what the query is trying to do. Perhaps some drastic reformulation is called for.
Anyway, it is a bad query... Don't GROUP BY one thing, but fetch several non-aggregate things. You will get unpredictable values for everything but table1_id.
More
It is unclear why you have two tables. The 3 steps are unclear. But here is a guess:
SELECT ch.*
FROM ( SELECT id,
price
FROM ( SELECT min(id) AS id
FROM history AS ah
WHERE created_at > NOW() - INTERVAL 24 HOUR
AND price != -1
) AS yesterday
JOIN history bh
WHERE bh.id < a.yesterday.id
AND bh.price != -1
ORDER BY bh.id DESC
LIMIT 1
) AS previous
JOIN history AS ch
WHERE ch.price != previous.price
AND ch.price != -1
AND ch.created_at > NOW() - INTERVAL 24 HOUR;
INDEX(created_at, id)
The innermost SELECT is item #1, etc.
Even if it is not the 'right' solution, maybe it will give you some clues.
answered Apr 25 '16 at 22:06
Rick JamesRick James
43.7k22259
Trying again...
– Rick James
Apr 27 '16 at 4:25
add a comment |
history: INDEX(created_reason, created_at)
Are you trying to avoid the "last record" via the price <>? If something else, then consider changing AND price <> ( SELECT ... LIMIT 1 ) to AND NOT EXISTS ( SELECT * ... )
Please explain, in words, what the query is trying to do. Perhaps some drastic reformulation is called for.
Anyway, it is a bad query... Don't GROUP BY one thing, but fetch several non-aggregate things. You will get unpredictable values for everything but table1_id.
More
It is unclear why you have two tables. The 3 steps are unclear. But here is a guess:
SELECT ch.*
FROM ( SELECT id,
price
FROM ( SELECT min(id) AS id
FROM history AS ah
WHERE created_at > NOW() - INTERVAL 24 HOUR
AND price != -1
) AS yesterday
JOIN history bh
WHERE bh.id < a.yesterday.id
AND bh.price != -1
ORDER BY bh.id DESC
LIMIT 1
) AS previous
JOIN history AS ch
WHERE ch.price != previous.price
AND ch.price != -1
AND ch.created_at > NOW() - INTERVAL 24 HOUR;
INDEX(created_at, id)
The innermost SELECT is item #1, etc.
Even if it is not the 'right' solution, maybe it will give you some clues.
answered Apr 25 '16 at 22:06
Rick JamesRick James
43.7k22259
Trying again...
– Rick James
Apr 27 '16 at 4:25
add a comment |
history: INDEX(created_reason, created_at)
Are you trying to avoid the "last record" via the price <>? If something else, then consider changing AND price <> ( SELECT ... LIMIT 1 ) to AND NOT EXISTS ( SELECT * ... )
Please explain, in words, what the query is trying to do. Perhaps some drastic reformulation is called for.
Anyway, it is a bad query... Don't GROUP BY one thing, but fetch several non-aggregate things. You will get unpredictable values for everything but table1_id.
More
It is unclear why you have two tables. The 3 steps are unclear. But here is a guess:
SELECT ch.*
FROM ( SELECT id,
price
FROM ( SELECT min(id) AS id
FROM history AS ah
WHERE created_at > NOW() - INTERVAL 24 HOUR
AND price != -1
) AS yesterday
JOIN history bh
WHERE bh.id < a.yesterday.id
AND bh.price != -1
ORDER BY bh.id DESC
LIMIT 1
) AS previous
JOIN history AS ch
WHERE ch.price != previous.price
AND ch.price != -1
AND ch.created_at > NOW() - INTERVAL 24 HOUR;
INDEX(created_at, id)
The innermost SELECT is item #1, etc.
Even if it is not the 'right' solution, maybe it will give you some clues.
answered Apr 25 '16 at 22:06
Rick JamesRick James
43.7k22259
history: INDEX(created_reason, created_at)
Are you trying to avoid the "last record" via the price <>? If something else, then consider changing AND price <> ( SELECT ... LIMIT 1 ) to AND NOT EXISTS ( SELECT * ... )
Please explain, in words, what the query is trying to do. Perhaps some drastic reformulation is called for.
Anyway, it is a bad query... Don't GROUP BY one thing, but fetch several non-aggregate things. You will get unpredictable values for everything but table1_id.
More
It is unclear why you have two tables. The 3 steps are unclear. But here is a guess:
SELECT ch.*
FROM ( SELECT id,
price
FROM ( SELECT min(id) AS id
FROM history AS ah
WHERE created_at > NOW() - INTERVAL 24 HOUR
AND price != -1
) AS yesterday
JOIN history bh
WHERE bh.id < a.yesterday.id
AND bh.price != -1
ORDER BY bh.id DESC
LIMIT 1
) AS previous
JOIN history AS ch
WHERE ch.price != previous.price
AND ch.price != -1
AND ch.created_at > NOW() - INTERVAL 24 HOUR;
INDEX(created_at, id)
The innermost SELECT is item #1, etc.
Even if it is not the 'right' solution, maybe it will give you some clues.
answered Apr 25 '16 at 22:06
Rick JamesRick James
43.7k22259
edited Apr 27 '16 at 4:25
answered Apr 25 '16 at 22:06
Rick JamesRick James
43.7k22259
answered Apr 25 '16 at 22:06
Rick JamesRick James
43.7k22259
answered Apr 25 '16 at 22:06
Rick JamesRick James
43.7k22259
43.7k22259
Trying again...
– Rick James
Apr 27 '16 at 4:25
add a comment |
Trying again...
– Rick James
Apr 27 '16 at 4:25
Trying again...
– Rick James
Apr 27 '16 at 4:25
Trying again...
– Rick James
Apr 27 '16 at 4:25
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%2f136425%2fneed-help-improving-query-performance%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
2
Edit your question with details of the real create table statements, and the plan for the actual query
– Philᵀᴹ
Apr 25 '16 at 9:14
2
Your sqlfiddle example contains no secondary indexes so we need to see the real structure (even if you might want to anonymise some column names). Use EXPLAIN to get the query plan.
– jkavalik
Apr 25 '16 at 10:12
In #2, do you want "the record that immediately precedes the oldest of the records in #1"?
– Rick James
Apr 26 '16 at 21:33
@RickJames Yes.
– kmligue
Apr 27 '16 at 3:46