Need help improving query performance The Next CEO of Stack OverflowSlow performance of MySQL...
Domestic-to-international connection at Orlando (MCO)
Necessary condition on homology group for a set to be contractible
Reference request: Grassmannian and Plucker coordinates in type B, C, D
Unclear about dynamic binding
Is it ever safe to open a suspicious HTML file (e.g. email attachment)?
Can we say or write : "No, it'sn't"?
Running a General Election and the European Elections together
Does Germany produce more waste than the US?
How did people program for Consoles with multiple CPUs?
Is it possible to use a NPN BJT as switch, from single power source?
Is it professional to write unrelated content in an almost-empty email?
Example of a Mathematician/Physicist whose Other Publications during their PhD eclipsed their PhD Thesis
Does increasing your ability score affect your main stat?
Where do students learn to solve polynomial equations these days?
Chain wire methods together in Lightning Web Components
Is wanting to ask what to write an indication that you need to change your story?
Is French Guiana a (hard) EU border?
How to edit “Name” property in GCI output?
What does "Its cash flow is deeply negative" mean?
How is this set of matrices closed under multiplication?
Why, when going from special to general relativity, do we just replace partial derivatives with covariant derivatives?
Recycling old answers
How to write a definition with variants?
Do I need to write [sic] when a number is less than 10 but isn't written out?
Need help improving query performance
The Next CEO of Stack OverflowSlow performance of MySQL Join QuerySQL - query multiplying records returned - Cartesian ProductHow to optimized mysql query having large datasetNeed help improving sql query performanceQuery with Indexes slower than without IndexesA multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?SQL Server spatial filter with additional conditions performance issueImproving slow query on large tables on Postgres 9.2Is there a fast way to find records that were created OR updated within a date range?Optimize schema and query for overlapping date ranges query
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♦ 10 mins 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♦ 10 mins 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♦ 10 mins 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♦ 10 mins 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