MySQL/MariaDB: Comparing performance between two queries
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
asked Aug 19 '18 at 7:20
ManngoManngo
80821124
bumped to the homepage by Community♦ 5 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 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
asked Aug 19 '18 at 7:20
ManngoManngo
80821124
bumped to the homepage by Community♦ 5 mins 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 theEXPLAINs, 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
add a comment |
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
asked Aug 19 '18 at 7:20
ManngoManngo
80821124
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
mysql query-performance explain
asked Aug 19 '18 at 7:20
ManngoManngo
80821124
asked Aug 19 '18 at 7:20
ManngoManngo
80821124
asked Aug 19 '18 at 7:20
ManngoManngo
80821124
asked Aug 19 '18 at 7:20
ManngoManngo
80821124
asked Aug 19 '18 at 7:20
ManngoManngo
80821124
80821124
bumped to the homepage by Community♦ 5 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♦ 5 mins 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 theEXPLAINs, 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
add a comment |
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 theEXPLAINs, 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
add a comment |
1 Answer
1
active
oldest
votes
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!
answered Aug 19 '18 at 18:44
ewramnerewramner
2365
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%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
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!
answered Aug 19 '18 at 18:44
ewramnerewramner
2365
add a comment |
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!
answered Aug 19 '18 at 18:44
ewramnerewramner
2365
add a comment |
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!
answered Aug 19 '18 at 18:44
ewramnerewramner
2365
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!
answered Aug 19 '18 at 18:44
ewramnerewramner
2365
answered Aug 19 '18 at 18:44
ewramnerewramner
2365
answered Aug 19 '18 at 18:44
ewramnerewramner
2365
answered Aug 19 '18 at 18:44
ewramnerewramner
2365
2365
add a comment |
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%2f215299%2fmysql-mariadb-comparing-performance-between-two-queries%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
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