Sfrgttk

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

0

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:

1. i want to get the records that meet a certain criteria, particularly that the created_at field is within the last 24 hours


2. i need to get the record that immediately precedes the records in #1


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

share|improve this question

edited Apr 26 '16 at 2:09

kmligue

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 | 

0

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:

1. i want to get the records that meet a certain criteria, particularly that the created_at field is within the last 24 hours


2. i need to get the record that immediately precedes the records in #1


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

share|improve this question

edited Apr 26 '16 at 2:09

kmligue

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:

1. i want to get the records that meet a certain criteria, particularly that the created_at field is within the last 24 hours


2. i need to get the record that immediately precedes the records in #1


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

share|improve this question

edited Apr 26 '16 at 2:09

kmligue

asked Apr 25 '16 at 8:28

kmliguekmligue

312

share|improve this question

edited Apr 26 '16 at 2:09

kmligue

asked Apr 25 '16 at 8:28

kmliguekmligue

312

share|improve this question

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

1 Answer
1

active

oldest

votes

0

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.

share|improve this answer

edited Apr 27 '16 at 4:25

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


}
});

draft saved

draft discarded

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

Name

Email

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

Email

Required, but never shown

0

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.

share|improve this answer

edited Apr 27 '16 at 4:25

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 | 

0

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.

share|improve this answer

edited Apr 27 '16 at 4:25

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.

share|improve this answer

edited Apr 27 '16 at 4:25

answered Apr 25 '16 at 22:06

Rick JamesRick James

43.7k22259

history:  INDEX(created_reason, created_at)

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)

share|improve this answer

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