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

edited Apr 26 '16 at 2:09

asked Apr 25 '16 at 8:28

kmligue

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

edited Apr 26 '16 at 2:09

asked Apr 25 '16 at 8:28

kmligue

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

edited Apr 26 '16 at 2:09

asked Apr 25 '16 at 8:28

kmligue

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

edited Apr 26 '16 at 2:09

asked Apr 25 '16 at 8:28

kmligue

312

edited Apr 26 '16 at 2:09

asked Apr 25 '16 at 8:28

kmligue

312

edited Apr 26 '16 at 2:09

asked Apr 25 '16 at 8:28

kmligue

312

asked Apr 25 '16 at 8:28

kmligue

312

asked Apr 25 '16 at 8:28

kmligue

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

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

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 |

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.

edited Apr 27 '16 at 4:25

answered Apr 25 '16 at 22:06

Rick 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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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.

edited Apr 27 '16 at 4:25

answered Apr 25 '16 at 22:06

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

edited Apr 27 '16 at 4:25

answered Apr 25 '16 at 22:06

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

edited Apr 27 '16 at 4:25

answered Apr 25 '16 at 22:06

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

edited Apr 27 '16 at 4:25

answered Apr 25 '16 at 22:06

Rick James

43.7k22259

edited Apr 27 '16 at 4:25

answered Apr 25 '16 at 22:06

Rick James

43.7k22259

answered Apr 25 '16 at 22:06

Rick James

43.7k22259

answered Apr 25 '16 at 22:06

Rick James

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

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Xrhrft