Does adding join condition on two different tables (excluding the table to be joined) slows down query and performance
I have 3 tables in mySQL => table1, table2 and table3 and the data in all three tables is large (>100k)
My join condition is :
select * from table1 t1
join table2 t2 on t1.col1 = t2.col1
join table3 t3 on t3.col2 = t2.col2 and t3.col3 = t1.col3
This query renders result very slow and according to me the issue is in the second join condition as if I remove the second condition, the query renders result instantly.
Can anyone please explain the reason of the query being slow?
Thanks in advance.
mysql database sql-server-2008 database-performance
edited Nov 11 '18 at 19:00
James Z
11.2k71935
asked Nov 11 '18 at 14:47
Pallavi GoyalPallavi Goyal
1
add a comment |
I have 3 tables in mySQL => table1, table2 and table3 and the data in all three tables is large (>100k)
My join condition is :
select * from table1 t1
join table2 t2 on t1.col1 = t2.col1
join table3 t3 on t3.col2 = t2.col2 and t3.col3 = t1.col3
This query renders result very slow and according to me the issue is in the second join condition as if I remove the second condition, the query renders result instantly.
Can anyone please explain the reason of the query being slow?
Thanks in advance.
mysql database sql-server-2008 database-performance
edited Nov 11 '18 at 19:00
James Z
11.2k71935
asked Nov 11 '18 at 14:47
Pallavi GoyalPallavi Goyal
1
Check the execution plan. Slowness may be because you don't have indexes on the joined columns or existing indexes are not being used. Also, avoidSELECT *unless you really need all columns from all tables referenced by the query.
– Dan Guzman
Nov 11 '18 at 14:51
1
Also, remove themysqltag from your question unless you are actually using MySQL instead of Microsoft SQL Server.
– Dan Guzman
Nov 11 '18 at 14:53
Please don't use Indian words like "lakh" here. That will just confuse people.
– James Z
Nov 11 '18 at 18:59
I do have indexes on the joined columns but they are not getting used..the solution to this which I found is to forcefully use the index using the USE/FORCE index syntax.
– Pallavi Goyal
Nov 12 '18 at 17:09
add a comment |
I have 3 tables in mySQL => table1, table2 and table3 and the data in all three tables is large (>100k)
My join condition is :
select * from table1 t1
join table2 t2 on t1.col1 = t2.col1
join table3 t3 on t3.col2 = t2.col2 and t3.col3 = t1.col3
This query renders result very slow and according to me the issue is in the second join condition as if I remove the second condition, the query renders result instantly.
Can anyone please explain the reason of the query being slow?
Thanks in advance.
mysql database sql-server-2008 database-performance
edited Nov 11 '18 at 19:00
James Z
11.2k71935
asked Nov 11 '18 at 14:47
Pallavi GoyalPallavi Goyal
1
I have 3 tables in mySQL => table1, table2 and table3 and the data in all three tables is large (>100k)
My join condition is :
select * from table1 t1
join table2 t2 on t1.col1 = t2.col1
join table3 t3 on t3.col2 = t2.col2 and t3.col3 = t1.col3
This query renders result very slow and according to me the issue is in the second join condition as if I remove the second condition, the query renders result instantly.
Can anyone please explain the reason of the query being slow?
Thanks in advance.
mysql database sql-server-2008 database-performance
mysql database sql-server-2008 database-performance
edited Nov 11 '18 at 19:00
James Z
11.2k71935
asked Nov 11 '18 at 14:47
Pallavi GoyalPallavi Goyal
1
edited Nov 11 '18 at 19:00
James Z
11.2k71935
asked Nov 11 '18 at 14:47
Pallavi GoyalPallavi Goyal
1
edited Nov 11 '18 at 19:00
James Z
11.2k71935
edited Nov 11 '18 at 19:00
James Z
11.2k71935
edited Nov 11 '18 at 19:00
James Z
11.2k71935
11.2k71935
asked Nov 11 '18 at 14:47
Pallavi GoyalPallavi Goyal
1
asked Nov 11 '18 at 14:47
Pallavi GoyalPallavi Goyal
1
asked Nov 11 '18 at 14:47
Pallavi GoyalPallavi Goyal
1
1
Check the execution plan. Slowness may be because you don't have indexes on the joined columns or existing indexes are not being used. Also, avoidSELECT *unless you really need all columns from all tables referenced by the query.
– Dan Guzman
Nov 11 '18 at 14:51
1
Also, remove themysqltag from your question unless you are actually using MySQL instead of Microsoft SQL Server.
– Dan Guzman
Nov 11 '18 at 14:53
Please don't use Indian words like "lakh" here. That will just confuse people.
– James Z
Nov 11 '18 at 18:59
I do have indexes on the joined columns but they are not getting used..the solution to this which I found is to forcefully use the index using the USE/FORCE index syntax.
– Pallavi Goyal
Nov 12 '18 at 17:09
add a comment |
Check the execution plan. Slowness may be because you don't have indexes on the joined columns or existing indexes are not being used. Also, avoidSELECT *unless you really need all columns from all tables referenced by the query.
– Dan Guzman
Nov 11 '18 at 14:51
1
Also, remove themysqltag from your question unless you are actually using MySQL instead of Microsoft SQL Server.
– Dan Guzman
Nov 11 '18 at 14:53
Please don't use Indian words like "lakh" here. That will just confuse people.
– James Z
Nov 11 '18 at 18:59
I do have indexes on the joined columns but they are not getting used..the solution to this which I found is to forcefully use the index using the USE/FORCE index syntax.
– Pallavi Goyal
Nov 12 '18 at 17:09
Check the execution plan. Slowness may be because you don't have indexes on the joined columns or existing indexes are not being used. Also, avoid
SELECT * unless you really need all columns from all tables referenced by the query.– Dan Guzman
Nov 11 '18 at 14:51
Check the execution plan. Slowness may be because you don't have indexes on the joined columns or existing indexes are not being used. Also, avoid
SELECT * unless you really need all columns from all tables referenced by the query.– Dan Guzman
Nov 11 '18 at 14:51
1
1
Also, remove the
mysql tag from your question unless you are actually using MySQL instead of Microsoft SQL Server.– Dan Guzman
Nov 11 '18 at 14:53
Also, remove the
mysql tag from your question unless you are actually using MySQL instead of Microsoft SQL Server.– Dan Guzman
Nov 11 '18 at 14:53
Please don't use Indian words like "lakh" here. That will just confuse people.
– James Z
Nov 11 '18 at 18:59
Please don't use Indian words like "lakh" here. That will just confuse people.
– James Z
Nov 11 '18 at 18:59
I do have indexes on the joined columns but they are not getting used..the solution to this which I found is to forcefully use the index using the USE/FORCE index syntax.
– Pallavi Goyal
Nov 12 '18 at 17:09
I do have indexes on the joined columns but they are not getting used..the solution to this which I found is to forcefully use the index using the USE/FORCE index syntax.
– Pallavi Goyal
Nov 12 '18 at 17:09
add a comment |
1 Answer
1
active
oldest
votes
Do you have these indexes?
table2: (col1)
table3: (col2, col3) -- in either order
Another tip: Don't use * (as in SELECT *) unless you really need all the columns. It prevents certain optimizations. If you want to discuss this further, please provide the real query and SHOW CREATE TABLE for each table.
If any of the columns used for joining are not the same datatype, character set, and collation, then indexes may not be useful.
Please provide EXPLAIN SELECT ...; it will give some clues we can discuss.
How many rows in the resultset? Sounds like over 100K? If so, then perhaps the network transfer time is the real slowdown?
answered Nov 11 '18 at 20:50
Rick JamesRick James
67.6k559100
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53249846%2fdoes-adding-join-condition-on-two-different-tables-excluding-the-table-to-be-jo%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
Do you have these indexes?
table2: (col1)
table3: (col2, col3) -- in either order
Another tip: Don't use * (as in SELECT *) unless you really need all the columns. It prevents certain optimizations. If you want to discuss this further, please provide the real query and SHOW CREATE TABLE for each table.
If any of the columns used for joining are not the same datatype, character set, and collation, then indexes may not be useful.
Please provide EXPLAIN SELECT ...; it will give some clues we can discuss.
How many rows in the resultset? Sounds like over 100K? If so, then perhaps the network transfer time is the real slowdown?
answered Nov 11 '18 at 20:50
Rick JamesRick James
67.6k559100
add a comment |
Do you have these indexes?
table2: (col1)
table3: (col2, col3) -- in either order
Another tip: Don't use * (as in SELECT *) unless you really need all the columns. It prevents certain optimizations. If you want to discuss this further, please provide the real query and SHOW CREATE TABLE for each table.
If any of the columns used for joining are not the same datatype, character set, and collation, then indexes may not be useful.
Please provide EXPLAIN SELECT ...; it will give some clues we can discuss.
How many rows in the resultset? Sounds like over 100K? If so, then perhaps the network transfer time is the real slowdown?
answered Nov 11 '18 at 20:50
Rick JamesRick James
67.6k559100
add a comment |
Do you have these indexes?
table2: (col1)
table3: (col2, col3) -- in either order
Another tip: Don't use * (as in SELECT *) unless you really need all the columns. It prevents certain optimizations. If you want to discuss this further, please provide the real query and SHOW CREATE TABLE for each table.
If any of the columns used for joining are not the same datatype, character set, and collation, then indexes may not be useful.
Please provide EXPLAIN SELECT ...; it will give some clues we can discuss.
How many rows in the resultset? Sounds like over 100K? If so, then perhaps the network transfer time is the real slowdown?
answered Nov 11 '18 at 20:50
Rick JamesRick James
67.6k559100
Do you have these indexes?
table2: (col1)
table3: (col2, col3) -- in either order
Another tip: Don't use * (as in SELECT *) unless you really need all the columns. It prevents certain optimizations. If you want to discuss this further, please provide the real query and SHOW CREATE TABLE for each table.
If any of the columns used for joining are not the same datatype, character set, and collation, then indexes may not be useful.
Please provide EXPLAIN SELECT ...; it will give some clues we can discuss.
How many rows in the resultset? Sounds like over 100K? If so, then perhaps the network transfer time is the real slowdown?
answered Nov 11 '18 at 20:50
Rick JamesRick James
67.6k559100
answered Nov 11 '18 at 20:50
Rick JamesRick James
67.6k559100
answered Nov 11 '18 at 20:50
Rick JamesRick James
67.6k559100
answered Nov 11 '18 at 20:50
Rick JamesRick James
67.6k559100
67.6k559100
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53249846%2fdoes-adding-join-condition-on-two-different-tables-excluding-the-table-to-be-jo%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
Check the execution plan. Slowness may be because you don't have indexes on the joined columns or existing indexes are not being used. Also, avoid
SELECT *unless you really need all columns from all tables referenced by the query.– Dan Guzman
Nov 11 '18 at 14:51
1
Also, remove the
mysqltag from your question unless you are actually using MySQL instead of Microsoft SQL Server.– Dan Guzman
Nov 11 '18 at 14:53
Please don't use Indian words like "lakh" here. That will just confuse people.
– James Z
Nov 11 '18 at 18:59
I do have indexes on the joined columns but they are not getting used..the solution to this which I found is to forcefully use the index using the USE/FORCE index syntax.
– Pallavi Goyal
Nov 12 '18 at 17:09