MySQL/MariaDB: Comparing performance between two queries












0















I am trying to compare the performance between two queries which give otherwise the same results.



The idea is that I want to compare using Windowing Functions available to newer versions to faking them with subqueries in older versions.



Here are the two queries:



select
*,
sum(total) over (order by id) as running,
sum(total) over (partition by ordered) as daily,
sum(total) over (partition by ordered order by id) as daily_running
from sales
order by id;

select
*,
(select sum(total) from sales s where s.id<=sales.id) as fake_running,
(select sum(total) from sales s where s.ordered=sales.ordered) as fake_daily,
(select sum(total) from sales s where s.ordered=sales.ordered and s.id<=sales.id) as fake_daily_running
from sales
order by id;


The results are identical.



I know that I can use EXPLAIN, but (a) I’m unclear how to interpret the results, and (b) I can’t see how to get a direct comparison between the two.



Microsoft SQL Management Studio has a tool which displays shows the estimated execution plan, and, most importantly, the cost of each query relative to the whole.



How can I get details on how the performance of the two queries would compare?










share|improve this question














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.
















  • You can compare the performance in practice only. On the real array of data. EXPLAIN will not help - it is used to optimize the single query, and all its data is relative, not absolute. PS. The change in source data (and/or its statistic) may fully change the EXPLAIN result for the same query...

    – Akina
    Aug 20 '18 at 5:59













  • The explain uses the same information as the query optimizer. While it is far from perfect it is often good enough, PROVIDED that the statistics are up-to-date and that the data is production-like and usually it is acceptable to run EXPLAIN in production. With outdated statistics and/or unrealistic data a practical test is just as bad. Start with the explain, which is cheap and easy to obtain; then by all means measure just in case.

    – ewramner
    Aug 20 '18 at 8:31











  • Well, let's see the EXPLAINs, maybe we can help. As for running timings -- be sure to have enough data, and for the data to realistic.

    – Rick James
    Aug 27 '18 at 13:01
















0















I am trying to compare the performance between two queries which give otherwise the same results.



The idea is that I want to compare using Windowing Functions available to newer versions to faking them with subqueries in older versions.



Here are the two queries:



select
*,
sum(total) over (order by id) as running,
sum(total) over (partition by ordered) as daily,
sum(total) over (partition by ordered order by id) as daily_running
from sales
order by id;

select
*,
(select sum(total) from sales s where s.id<=sales.id) as fake_running,
(select sum(total) from sales s where s.ordered=sales.ordered) as fake_daily,
(select sum(total) from sales s where s.ordered=sales.ordered and s.id<=sales.id) as fake_daily_running
from sales
order by id;


The results are identical.



I know that I can use EXPLAIN, but (a) I’m unclear how to interpret the results, and (b) I can’t see how to get a direct comparison between the two.



Microsoft SQL Management Studio has a tool which displays shows the estimated execution plan, and, most importantly, the cost of each query relative to the whole.



How can I get details on how the performance of the two queries would compare?










share|improve this question














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.
















  • You can compare the performance in practice only. On the real array of data. EXPLAIN will not help - it is used to optimize the single query, and all its data is relative, not absolute. PS. The change in source data (and/or its statistic) may fully change the EXPLAIN result for the same query...

    – Akina
    Aug 20 '18 at 5:59













  • The explain uses the same information as the query optimizer. While it is far from perfect it is often good enough, PROVIDED that the statistics are up-to-date and that the data is production-like and usually it is acceptable to run EXPLAIN in production. With outdated statistics and/or unrealistic data a practical test is just as bad. Start with the explain, which is cheap and easy to obtain; then by all means measure just in case.

    – ewramner
    Aug 20 '18 at 8:31











  • Well, let's see the EXPLAINs, maybe we can help. As for running timings -- be sure to have enough data, and for the data to realistic.

    – Rick James
    Aug 27 '18 at 13:01














0












0








0


1






I am trying to compare the performance between two queries which give otherwise the same results.



The idea is that I want to compare using Windowing Functions available to newer versions to faking them with subqueries in older versions.



Here are the two queries:



select
*,
sum(total) over (order by id) as running,
sum(total) over (partition by ordered) as daily,
sum(total) over (partition by ordered order by id) as daily_running
from sales
order by id;

select
*,
(select sum(total) from sales s where s.id<=sales.id) as fake_running,
(select sum(total) from sales s where s.ordered=sales.ordered) as fake_daily,
(select sum(total) from sales s where s.ordered=sales.ordered and s.id<=sales.id) as fake_daily_running
from sales
order by id;


The results are identical.



I know that I can use EXPLAIN, but (a) I’m unclear how to interpret the results, and (b) I can’t see how to get a direct comparison between the two.



Microsoft SQL Management Studio has a tool which displays shows the estimated execution plan, and, most importantly, the cost of each query relative to the whole.



How can I get details on how the performance of the two queries would compare?










share|improve this question














I am trying to compare the performance between two queries which give otherwise the same results.



The idea is that I want to compare using Windowing Functions available to newer versions to faking them with subqueries in older versions.



Here are the two queries:



select
*,
sum(total) over (order by id) as running,
sum(total) over (partition by ordered) as daily,
sum(total) over (partition by ordered order by id) as daily_running
from sales
order by id;

select
*,
(select sum(total) from sales s where s.id<=sales.id) as fake_running,
(select sum(total) from sales s where s.ordered=sales.ordered) as fake_daily,
(select sum(total) from sales s where s.ordered=sales.ordered and s.id<=sales.id) as fake_daily_running
from sales
order by id;


The results are identical.



I know that I can use EXPLAIN, but (a) I’m unclear how to interpret the results, and (b) I can’t see how to get a direct comparison between the two.



Microsoft SQL Management Studio has a tool which displays shows the estimated execution plan, and, most importantly, the cost of each query relative to the whole.



How can I get details on how the performance of the two queries would compare?







mysql query-performance explain






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Aug 19 '18 at 7:20









ManngoManngo

80821124




80821124





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.















  • You can compare the performance in practice only. On the real array of data. EXPLAIN will not help - it is used to optimize the single query, and all its data is relative, not absolute. PS. The change in source data (and/or its statistic) may fully change the EXPLAIN result for the same query...

    – Akina
    Aug 20 '18 at 5:59













  • The explain uses the same information as the query optimizer. While it is far from perfect it is often good enough, PROVIDED that the statistics are up-to-date and that the data is production-like and usually it is acceptable to run EXPLAIN in production. With outdated statistics and/or unrealistic data a practical test is just as bad. Start with the explain, which is cheap and easy to obtain; then by all means measure just in case.

    – ewramner
    Aug 20 '18 at 8:31











  • Well, let's see the EXPLAINs, maybe we can help. As for running timings -- be sure to have enough data, and for the data to realistic.

    – Rick James
    Aug 27 '18 at 13:01



















  • You can compare the performance in practice only. On the real array of data. EXPLAIN will not help - it is used to optimize the single query, and all its data is relative, not absolute. PS. The change in source data (and/or its statistic) may fully change the EXPLAIN result for the same query...

    – Akina
    Aug 20 '18 at 5:59













  • The explain uses the same information as the query optimizer. While it is far from perfect it is often good enough, PROVIDED that the statistics are up-to-date and that the data is production-like and usually it is acceptable to run EXPLAIN in production. With outdated statistics and/or unrealistic data a practical test is just as bad. Start with the explain, which is cheap and easy to obtain; then by all means measure just in case.

    – ewramner
    Aug 20 '18 at 8:31











  • Well, let's see the EXPLAINs, maybe we can help. As for running timings -- be sure to have enough data, and for the data to realistic.

    – Rick James
    Aug 27 '18 at 13:01

















You can compare the performance in practice only. On the real array of data. EXPLAIN will not help - it is used to optimize the single query, and all its data is relative, not absolute. PS. The change in source data (and/or its statistic) may fully change the EXPLAIN result for the same query...

– Akina
Aug 20 '18 at 5:59







You can compare the performance in practice only. On the real array of data. EXPLAIN will not help - it is used to optimize the single query, and all its data is relative, not absolute. PS. The change in source data (and/or its statistic) may fully change the EXPLAIN result for the same query...

– Akina
Aug 20 '18 at 5:59















The explain uses the same information as the query optimizer. While it is far from perfect it is often good enough, PROVIDED that the statistics are up-to-date and that the data is production-like and usually it is acceptable to run EXPLAIN in production. With outdated statistics and/or unrealistic data a practical test is just as bad. Start with the explain, which is cheap and easy to obtain; then by all means measure just in case.

– ewramner
Aug 20 '18 at 8:31





The explain uses the same information as the query optimizer. While it is far from perfect it is often good enough, PROVIDED that the statistics are up-to-date and that the data is production-like and usually it is acceptable to run EXPLAIN in production. With outdated statistics and/or unrealistic data a practical test is just as bad. Start with the explain, which is cheap and easy to obtain; then by all means measure just in case.

– ewramner
Aug 20 '18 at 8:31













Well, let's see the EXPLAINs, maybe we can help. As for running timings -- be sure to have enough data, and for the data to realistic.

– Rick James
Aug 27 '18 at 13:01





Well, let's see the EXPLAINs, maybe we can help. As for running timings -- be sure to have enough data, and for the data to realistic.

– Rick James
Aug 27 '18 at 13:01










1 Answer
1






active

oldest

votes


















0














You should use EXPLAIN, or by all means test with realistic data. Microsoft's tool is nice, but it is basically the same thing under the hood. The classic explain syntax (EXPLAIN SELECT ...) does not include cost, but it does include the estimated number of rows for each step. Better, you can use JSON to get more details:



EXPLAIN FORMAT=JSON SELECT ...


This will return a JSON document that includes query_cost. You don't get a fancy graph, but you can compare the estimated cost. Good luck!






share|improve this answer























    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


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215299%2fmysql-mariadb-comparing-performance-between-two-queries%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









    0














    You should use EXPLAIN, or by all means test with realistic data. Microsoft's tool is nice, but it is basically the same thing under the hood. The classic explain syntax (EXPLAIN SELECT ...) does not include cost, but it does include the estimated number of rows for each step. Better, you can use JSON to get more details:



    EXPLAIN FORMAT=JSON SELECT ...


    This will return a JSON document that includes query_cost. You don't get a fancy graph, but you can compare the estimated cost. Good luck!






    share|improve this answer




























      0














      You should use EXPLAIN, or by all means test with realistic data. Microsoft's tool is nice, but it is basically the same thing under the hood. The classic explain syntax (EXPLAIN SELECT ...) does not include cost, but it does include the estimated number of rows for each step. Better, you can use JSON to get more details:



      EXPLAIN FORMAT=JSON SELECT ...


      This will return a JSON document that includes query_cost. You don't get a fancy graph, but you can compare the estimated cost. Good luck!






      share|improve this answer


























        0












        0








        0







        You should use EXPLAIN, or by all means test with realistic data. Microsoft's tool is nice, but it is basically the same thing under the hood. The classic explain syntax (EXPLAIN SELECT ...) does not include cost, but it does include the estimated number of rows for each step. Better, you can use JSON to get more details:



        EXPLAIN FORMAT=JSON SELECT ...


        This will return a JSON document that includes query_cost. You don't get a fancy graph, but you can compare the estimated cost. Good luck!






        share|improve this answer













        You should use EXPLAIN, or by all means test with realistic data. Microsoft's tool is nice, but it is basically the same thing under the hood. The classic explain syntax (EXPLAIN SELECT ...) does not include cost, but it does include the estimated number of rows for each step. Better, you can use JSON to get more details:



        EXPLAIN FORMAT=JSON SELECT ...


        This will return a JSON document that includes query_cost. You don't get a fancy graph, but you can compare the estimated cost. Good luck!







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Aug 19 '18 at 18:44









        ewramnerewramner

        2365




        2365






























            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














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215299%2fmysql-mariadb-comparing-performance-between-two-queries%23new-answer', 'question_page');
            }
            );

            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







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh