Which is more efficient: A join or a group by?Is SELECT COUNT GROUP BY more efficient than counting a result...
Forgetting the musical notes while performing in concert
Can the Meissner effect explain very large floating structures?
Can I run a new neutral wire to repair a broken circuit?
Why no variance term in Bayesian logistic regression?
In 'Revenger,' what does 'cove' come from?
Extract rows of a table, that include less than x NULLs
Why doesn't using multiple commands with a || or && conditional work?
Apex Framework / library for consuming REST services
How could indestructible materials be used in power generation?
Why would the Red Woman birth a shadow if she worshipped the Lord of the Light?
How does a predictive coding aid in lossless compression?
iPad being using in wall mount battery swollen
What is a romance in Latin?
ssTTsSTtRrriinInnnnNNNIiinngg
Valid term from quadratic sequence?
Alternative to sending password over mail?
Why is this clock signal connected to a capacitor to gnd?
Personal Teleportation: From Rags to Riches
How to prevent "they're falling in love" trope
Venezuelan girlfriend wants to travel the USA to be with me. What is the process?
Ambiguity in the definition of entropy
Is there a hemisphere-neutral way of specifying a season?
Should I tell management that I intend to leave due to bad software development practices?
Short story with a alien planet, government officials must wear exploding medallions
Which is more efficient: A join or a group by?
Is SELECT COUNT GROUP BY more efficient than counting a result set?MySQL group concat not showing distinct values when joined to another tableEfficient matching rows across two tables with huge data setMySQL - Group By - How does it determine which row to return from joined tableWhy is a query with a join and limit clause running so slow, even using a covering index?Which join to use?How can I make this aggregation query more efficient?Which query is optimizedGROUP_CONCAT without limit by WHERESQL Grouping rows in a feed database table, output only grouped rows adding a group row
I've got a table A with a relation to another table B, where each A row has two rows in B. I want to extract all the values from a specific column in B for each row in A.
Ignoring the difference in output format (this is just for illustration), is it more efficient to join the table twice with SELECT b1.col, b2.col ..., or to use a group and do SELECT GROUP_CONCAT(b.col) ...?
mysql optimization
asked Jul 26 '16 at 9:38
BenubirdBenubird
18015
bumped to the homepage by Community♦ 3 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've got a table A with a relation to another table B, where each A row has two rows in B. I want to extract all the values from a specific column in B for each row in A.
Ignoring the difference in output format (this is just for illustration), is it more efficient to join the table twice with SELECT b1.col, b2.col ..., or to use a group and do SELECT GROUP_CONCAT(b.col) ...?
mysql optimization
asked Jul 26 '16 at 9:38
BenubirdBenubird
18015
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Please spell out the suggested queries; there are too many possible ways to do each case. AlsoSHOW CREATE TABLEto make it clear what indexes are available to theJOIN-- this is critical to performance of it, and may impact theGROUP BY.
– Rick James
Jul 26 '16 at 16:42
@RickJames This is a question about general efficiency, I don't have specific queries in mind. Can you explain how indices can impact the group by different from the join so as to change the relative efficiency of the queries? That would make a good answer.
– Benubird
Jul 27 '16 at 9:07
add a comment |
I've got a table A with a relation to another table B, where each A row has two rows in B. I want to extract all the values from a specific column in B for each row in A.
Ignoring the difference in output format (this is just for illustration), is it more efficient to join the table twice with SELECT b1.col, b2.col ..., or to use a group and do SELECT GROUP_CONCAT(b.col) ...?
mysql optimization
asked Jul 26 '16 at 9:38
BenubirdBenubird
18015
I've got a table A with a relation to another table B, where each A row has two rows in B. I want to extract all the values from a specific column in B for each row in A.
Ignoring the difference in output format (this is just for illustration), is it more efficient to join the table twice with SELECT b1.col, b2.col ..., or to use a group and do SELECT GROUP_CONCAT(b.col) ...?
mysql optimization
mysql optimization
asked Jul 26 '16 at 9:38
BenubirdBenubird
18015
asked Jul 26 '16 at 9:38
BenubirdBenubird
18015
asked Jul 26 '16 at 9:38
BenubirdBenubird
18015
asked Jul 26 '16 at 9:38
BenubirdBenubird
18015
asked Jul 26 '16 at 9:38
BenubirdBenubird
18015
18015
bumped to the homepage by Community♦ 3 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♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Please spell out the suggested queries; there are too many possible ways to do each case. AlsoSHOW CREATE TABLEto make it clear what indexes are available to theJOIN-- this is critical to performance of it, and may impact theGROUP BY.
– Rick James
Jul 26 '16 at 16:42
@RickJames This is a question about general efficiency, I don't have specific queries in mind. Can you explain how indices can impact the group by different from the join so as to change the relative efficiency of the queries? That would make a good answer.
– Benubird
Jul 27 '16 at 9:07
add a comment |
1
Please spell out the suggested queries; there are too many possible ways to do each case. AlsoSHOW CREATE TABLEto make it clear what indexes are available to theJOIN-- this is critical to performance of it, and may impact theGROUP BY.
– Rick James
Jul 26 '16 at 16:42
@RickJames This is a question about general efficiency, I don't have specific queries in mind. Can you explain how indices can impact the group by different from the join so as to change the relative efficiency of the queries? That would make a good answer.
– Benubird
Jul 27 '16 at 9:07
1
1
Please spell out the suggested queries; there are too many possible ways to do each case. Also
SHOW CREATE TABLE to make it clear what indexes are available to the JOIN -- this is critical to performance of it, and may impact the GROUP BY.– Rick James
Jul 26 '16 at 16:42
Please spell out the suggested queries; there are too many possible ways to do each case. Also
SHOW CREATE TABLE to make it clear what indexes are available to the JOIN -- this is critical to performance of it, and may impact the GROUP BY.– Rick James
Jul 26 '16 at 16:42
@RickJames This is a question about general efficiency, I don't have specific queries in mind. Can you explain how indices can impact the group by different from the join so as to change the relative efficiency of the queries? That would make a good answer.
– Benubird
Jul 27 '16 at 9:07
@RickJames This is a question about general efficiency, I don't have specific queries in mind. Can you explain how indices can impact the group by different from the join so as to change the relative efficiency of the queries? That would make a good answer.
– Benubird
Jul 27 '16 at 9:07
add a comment |
1 Answer
1
active
oldest
votes
You are doing a form of "pivoting"?
Two JOINs gives you two columns. GROUP_CONCAT gives you one column with some delimiter between the items. If you have a business reason for one versus the other, that will be the deciding factor.
If either "format" of the output is acceptable (which is assume you are assuming), then the next question is whether you have the 'right' indexes. Both will need the suitable index for reaching into B. So let's assume you have that, too.
The answer is still not obvious, since the two queries will be performed differently.
The double-join may have a problem. How to you picking one row's col for b1 and a different row's col for b2? OK, I'll ignore that puzzle, and move on.
(Maybe now you see why I wanted more detail?)
The double-join may be 'better' if the two probes are efficient enough. But 2 joins seems inefficient.
The group_concat may be 'better' because of hitting only one table. But it probably involves an extra sort (depending on other details of the queries).
My gut says that there will be cases where the double-join will win, and cases where the other wins.
answered Jul 27 '16 at 15:46
Rick JamesRick James
43.7k22259
Ok, here's an example query:select a.name, b1.val, b2.val from a join b as b1 on a.key = b1.key and b1.type = 'one' join b as b2 on a.key = b2.key and b2.type = 'two'. I don't see how using a pivot table would affect the difference between joins and groups though.
– Benubird
Jul 28 '16 at 8:28
This is the same information, but without pivoting: SELECT a.name, b.type, b.val FROM a JOIN b WHERE a.key = b.key;
– Rick James
Jul 29 '16 at 17:00
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%2f144922%2fwhich-is-more-efficient-a-join-or-a-group-by%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 are doing a form of "pivoting"?
Two JOINs gives you two columns. GROUP_CONCAT gives you one column with some delimiter between the items. If you have a business reason for one versus the other, that will be the deciding factor.
If either "format" of the output is acceptable (which is assume you are assuming), then the next question is whether you have the 'right' indexes. Both will need the suitable index for reaching into B. So let's assume you have that, too.
The answer is still not obvious, since the two queries will be performed differently.
The double-join may have a problem. How to you picking one row's col for b1 and a different row's col for b2? OK, I'll ignore that puzzle, and move on.
(Maybe now you see why I wanted more detail?)
The double-join may be 'better' if the two probes are efficient enough. But 2 joins seems inefficient.
The group_concat may be 'better' because of hitting only one table. But it probably involves an extra sort (depending on other details of the queries).
My gut says that there will be cases where the double-join will win, and cases where the other wins.
answered Jul 27 '16 at 15:46
Rick JamesRick James
43.7k22259
Ok, here's an example query:select a.name, b1.val, b2.val from a join b as b1 on a.key = b1.key and b1.type = 'one' join b as b2 on a.key = b2.key and b2.type = 'two'. I don't see how using a pivot table would affect the difference between joins and groups though.
– Benubird
Jul 28 '16 at 8:28
This is the same information, but without pivoting: SELECT a.name, b.type, b.val FROM a JOIN b WHERE a.key = b.key;
– Rick James
Jul 29 '16 at 17:00
add a comment |
You are doing a form of "pivoting"?
Two JOINs gives you two columns. GROUP_CONCAT gives you one column with some delimiter between the items. If you have a business reason for one versus the other, that will be the deciding factor.
If either "format" of the output is acceptable (which is assume you are assuming), then the next question is whether you have the 'right' indexes. Both will need the suitable index for reaching into B. So let's assume you have that, too.
The answer is still not obvious, since the two queries will be performed differently.
The double-join may have a problem. How to you picking one row's col for b1 and a different row's col for b2? OK, I'll ignore that puzzle, and move on.
(Maybe now you see why I wanted more detail?)
The double-join may be 'better' if the two probes are efficient enough. But 2 joins seems inefficient.
The group_concat may be 'better' because of hitting only one table. But it probably involves an extra sort (depending on other details of the queries).
My gut says that there will be cases where the double-join will win, and cases where the other wins.
answered Jul 27 '16 at 15:46
Rick JamesRick James
43.7k22259
Ok, here's an example query:select a.name, b1.val, b2.val from a join b as b1 on a.key = b1.key and b1.type = 'one' join b as b2 on a.key = b2.key and b2.type = 'two'. I don't see how using a pivot table would affect the difference between joins and groups though.
– Benubird
Jul 28 '16 at 8:28
This is the same information, but without pivoting: SELECT a.name, b.type, b.val FROM a JOIN b WHERE a.key = b.key;
– Rick James
Jul 29 '16 at 17:00
add a comment |
You are doing a form of "pivoting"?
Two JOINs gives you two columns. GROUP_CONCAT gives you one column with some delimiter between the items. If you have a business reason for one versus the other, that will be the deciding factor.
If either "format" of the output is acceptable (which is assume you are assuming), then the next question is whether you have the 'right' indexes. Both will need the suitable index for reaching into B. So let's assume you have that, too.
The answer is still not obvious, since the two queries will be performed differently.
The double-join may have a problem. How to you picking one row's col for b1 and a different row's col for b2? OK, I'll ignore that puzzle, and move on.
(Maybe now you see why I wanted more detail?)
The double-join may be 'better' if the two probes are efficient enough. But 2 joins seems inefficient.
The group_concat may be 'better' because of hitting only one table. But it probably involves an extra sort (depending on other details of the queries).
My gut says that there will be cases where the double-join will win, and cases where the other wins.
answered Jul 27 '16 at 15:46
Rick JamesRick James
43.7k22259
You are doing a form of "pivoting"?
Two JOINs gives you two columns. GROUP_CONCAT gives you one column with some delimiter between the items. If you have a business reason for one versus the other, that will be the deciding factor.
If either "format" of the output is acceptable (which is assume you are assuming), then the next question is whether you have the 'right' indexes. Both will need the suitable index for reaching into B. So let's assume you have that, too.
The answer is still not obvious, since the two queries will be performed differently.
The double-join may have a problem. How to you picking one row's col for b1 and a different row's col for b2? OK, I'll ignore that puzzle, and move on.
(Maybe now you see why I wanted more detail?)
The double-join may be 'better' if the two probes are efficient enough. But 2 joins seems inefficient.
The group_concat may be 'better' because of hitting only one table. But it probably involves an extra sort (depending on other details of the queries).
My gut says that there will be cases where the double-join will win, and cases where the other wins.
answered Jul 27 '16 at 15:46
Rick JamesRick James
43.7k22259
answered Jul 27 '16 at 15:46
Rick JamesRick James
43.7k22259
answered Jul 27 '16 at 15:46
Rick JamesRick James
43.7k22259
answered Jul 27 '16 at 15:46
Rick JamesRick James
43.7k22259
43.7k22259
Ok, here's an example query:select a.name, b1.val, b2.val from a join b as b1 on a.key = b1.key and b1.type = 'one' join b as b2 on a.key = b2.key and b2.type = 'two'. I don't see how using a pivot table would affect the difference between joins and groups though.
– Benubird
Jul 28 '16 at 8:28
This is the same information, but without pivoting: SELECT a.name, b.type, b.val FROM a JOIN b WHERE a.key = b.key;
– Rick James
Jul 29 '16 at 17:00
add a comment |
Ok, here's an example query:select a.name, b1.val, b2.val from a join b as b1 on a.key = b1.key and b1.type = 'one' join b as b2 on a.key = b2.key and b2.type = 'two'. I don't see how using a pivot table would affect the difference between joins and groups though.
– Benubird
Jul 28 '16 at 8:28
This is the same information, but without pivoting: SELECT a.name, b.type, b.val FROM a JOIN b WHERE a.key = b.key;
– Rick James
Jul 29 '16 at 17:00
Ok, here's an example query:
select a.name, b1.val, b2.val from a join b as b1 on a.key = b1.key and b1.type = 'one' join b as b2 on a.key = b2.key and b2.type = 'two'. I don't see how using a pivot table would affect the difference between joins and groups though.– Benubird
Jul 28 '16 at 8:28
Ok, here's an example query:
select a.name, b1.val, b2.val from a join b as b1 on a.key = b1.key and b1.type = 'one' join b as b2 on a.key = b2.key and b2.type = 'two'. I don't see how using a pivot table would affect the difference between joins and groups though.– Benubird
Jul 28 '16 at 8:28
This is the same information, but without pivoting: SELECT a.name, b.type, b.val FROM a JOIN b WHERE a.key = b.key;
– Rick James
Jul 29 '16 at 17:00
This is the same information, but without pivoting: SELECT a.name, b.type, b.val FROM a JOIN b WHERE a.key = b.key;
– Rick James
Jul 29 '16 at 17:00
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%2f144922%2fwhich-is-more-efficient-a-join-or-a-group-by%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
1
Please spell out the suggested queries; there are too many possible ways to do each case. Also
SHOW CREATE TABLEto make it clear what indexes are available to theJOIN-- this is critical to performance of it, and may impact theGROUP BY.– Rick James
Jul 26 '16 at 16:42
@RickJames This is a question about general efficiency, I don't have specific queries in mind. Can you explain how indices can impact the group by different from the join so as to change the relative efficiency of the queries? That would make a good answer.
– Benubird
Jul 27 '16 at 9:07