Sfrgttk

Why would the Pakistan airspace closure cancel flights not headed to Pakistan itself?

Closed form for these polynomials?

Why avoid shared user accounts?

"On one hand" vs "on the one hand."

Can polymorphing monsters spam their ability to effectively give themselves a massive health pool?

What's a good word to describe a public place that looks like it wouldn't be rough?

How did the original light saber work?

It took me a lot of time to make this, pls like. (YouTube Comments #1)

What makes the Forgotten Realms "forgotten"?

Am I a Rude Number?

Do authors have to be politically correct in article-writing?

What's the most convenient time of year in the USA to end the world?

Are there any monsters that consume a player character?

Why is button three on trumpet almost never used alone?

Is there a better way to make this?

Compound Interest... with Wizard Money

How to deal with an incendiary email that was recalled

Issues with new Macs: Hardware makes them difficult for me to use. What options might be available in the future?

What is the wife of a henpecked husband called?

Why did Jodrell Bank assist the Soviet Union to collect data from their spacecraft in the mid 1960's?

Number of FLOP (Floating Point Operations) for exponentiation

Approaches to criticizing short fiction

What kind of hardware implements Fourier transform?

High pressure canisters of air as gun-less projectiles

LEFT JOIN performance for WHERE on secondary table

Slow performance of MySQL Join QueryHow to use and optimize subquery on 100 million rowsUnderstanding Indexes and using them betterInnoDB + TokuDB fills up RAM then start swappingFastest engine for Mass Updates (only updates done on table)Performance hits with MySQL Left Joins and Order ByCreate index for multiple WHERE EXISTS subqueriesHandling a very large MySQL databasePerformance tuning for MySQL database (read only)Improving join query performance with limit and index

0

I've been developing for years and through out the carrier I've been using only LEFT JOIN as it provides a more logical sense of mapping my one to many data.

However recently I ran across a performance thought:

Say I have table user with millions of rows and table gender with only 2 rows

user                                gender

=====================               ================

id name      genderId               id gender show

=====================               ================

1  Sam       1                      1  Male   0

2  Samantha  2                      2  Female 1

3  Another millions of records

If I'm doing

select * from user u left join gender g on u.genderId = g.id where g.show = 1;

Does SQL engine joins the whole million user records first, then scan the million rows of records for join projected value?

Or is the engine smart enough to query and join only the rows with correct gender? Or is RIGHT JOIN the correct method to provide best performance?

If the behavior is dependent on engine type, let's talk about MySQL InnoDB.

mysql

share|improve this question

edited Oct 19 '18 at 6:40

a_horse_with_no_name

40.2k777112

asked Oct 19 '18 at 1:27

Chor Wai ChunChor Wai Chun

1012

bumped to the homepage by Community♦ 7 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
  

  
  
  
  
  
  

  
  
  Show the EXPLAIN query output. Even if you have a gender.id pinned at 2, genderId in user may not be indexed, and even if it was it would be a large proportion of a secondary index, and you still need to look up name and id, so it might not be used. LEFT vs RIGHT won't matter at all. LEFT JOIN should be used when there isn't always RHS match, rather than just one to many, even a basic INNER JOIN does many to many.
  
  – danblack
  Oct 19 '18 at 1:59
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Your join is INNER JOIN in practice, because a condition by right table presents in WHERE clause.
  
  – Akina
  Oct 19 '18 at 5:27
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Your where condition on the outer joined table turns the outer join back into an inner join
  
  – a_horse_with_no_name
  Oct 19 '18 at 6:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  DBMS engines are not smart enough to fetch only required entries. Bad news is that nobody is smart enough. Mankind is not able to operate sets at all. All we can do is to emulate the sets by lists and perform the search instead of fetch. Therefore the correct question should sound like that: "Why this query over that tables with that indexing and that cardinalities is slow?"
  
  – Kondybas
  Oct 19 '18 at 12:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you want to happen when user.gender_id = 3 and there is no corresponding row in the table gender.
  
  – Rick James
  Oct 22 '18 at 4:16
  

  
  
  
  

add a comment | 

0

I've been developing for years and through out the carrier I've been using only LEFT JOIN as it provides a more logical sense of mapping my one to many data.

However recently I ran across a performance thought:

Say I have table user with millions of rows and table gender with only 2 rows

user                                gender

=====================               ================

id name      genderId               id gender show

=====================               ================

1  Sam       1                      1  Male   0

2  Samantha  2                      2  Female 1

3  Another millions of records

If I'm doing

select * from user u left join gender g on u.genderId = g.id where g.show = 1;

Does SQL engine joins the whole million user records first, then scan the million rows of records for join projected value?

Or is the engine smart enough to query and join only the rows with correct gender? Or is RIGHT JOIN the correct method to provide best performance?

If the behavior is dependent on engine type, let's talk about MySQL InnoDB.

mysql

share|improve this question

edited Oct 19 '18 at 6:40

a_horse_with_no_name

40.2k777112

asked Oct 19 '18 at 1:27

Chor Wai ChunChor Wai Chun

1012

bumped to the homepage by Community♦ 7 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
  

  
  
  
  
  
  

  
  
  Show the EXPLAIN query output. Even if you have a gender.id pinned at 2, genderId in user may not be indexed, and even if it was it would be a large proportion of a secondary index, and you still need to look up name and id, so it might not be used. LEFT vs RIGHT won't matter at all. LEFT JOIN should be used when there isn't always RHS match, rather than just one to many, even a basic INNER JOIN does many to many.
  
  – danblack
  Oct 19 '18 at 1:59
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Your join is INNER JOIN in practice, because a condition by right table presents in WHERE clause.
  
  – Akina
  Oct 19 '18 at 5:27
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Your where condition on the outer joined table turns the outer join back into an inner join
  
  – a_horse_with_no_name
  Oct 19 '18 at 6:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  DBMS engines are not smart enough to fetch only required entries. Bad news is that nobody is smart enough. Mankind is not able to operate sets at all. All we can do is to emulate the sets by lists and perform the search instead of fetch. Therefore the correct question should sound like that: "Why this query over that tables with that indexing and that cardinalities is slow?"
  
  – Kondybas
  Oct 19 '18 at 12:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you want to happen when user.gender_id = 3 and there is no corresponding row in the table gender.
  
  – Rick James
  Oct 22 '18 at 4:16
  

  
  
  
  

add a comment | 

I've been developing for years and through out the carrier I've been using only LEFT JOIN as it provides a more logical sense of mapping my one to many data.

However recently I ran across a performance thought:

Say I have table user with millions of rows and table gender with only 2 rows

user                                gender

=====================               ================

id name      genderId               id gender show

=====================               ================

1  Sam       1                      1  Male   0

2  Samantha  2                      2  Female 1

3  Another millions of records

If I'm doing

select * from user u left join gender g on u.genderId = g.id where g.show = 1;

Does SQL engine joins the whole million user records first, then scan the million rows of records for join projected value?

Or is the engine smart enough to query and join only the rows with correct gender? Or is RIGHT JOIN the correct method to provide best performance?

If the behavior is dependent on engine type, let's talk about MySQL InnoDB.

mysql

share|improve this question

edited Oct 19 '18 at 6:40

a_horse_with_no_name

40.2k777112

asked Oct 19 '18 at 1:27

Chor Wai ChunChor Wai Chun

1012

user                                gender

=====================               ================

id name      genderId               id gender show

=====================               ================

1  Sam       1                      1  Male   0

2  Samantha  2                      2  Female 1

3  Another millions of records

select * from user u left join gender g on u.genderId = g.id where g.show = 1;

share|improve this question

edited Oct 19 '18 at 6:40

a_horse_with_no_name

40.2k777112

asked Oct 19 '18 at 1:27

Chor Wai ChunChor Wai Chun

1012

share|improve this question

edited Oct 19 '18 at 6:40

a_horse_with_no_name

40.2k777112

asked Oct 19 '18 at 1:27

Chor Wai ChunChor Wai Chun

1012

edited Oct 19 '18 at 6:40

a_horse_with_no_name

40.2k777112

edited Oct 19 '18 at 6:40

a_horse_with_no_name

40.2k777112

asked Oct 19 '18 at 1:27

Chor Wai ChunChor Wai Chun

1012

asked Oct 19 '18 at 1:27

Chor Wai ChunChor Wai Chun

1012

1 Answer
1

active

oldest

votes

0

Remember how SQL works: construct a set of tuples (rows) based on the given tables and joins, then filter those based on any where clause.

Because you have a where condition on the "right" table, your outer join is effectively converted into an inner join.

To retain the "outer-ness" of the join, put the filter condition into the join construct, rather than in the where clause:

select * 

from      user u 

left join gender g 

     on  u.genderId = g.id 

     and g.show = 1 

[where ...] 

share|improve this answer

answered Oct 19 '18 at 11:18

Phill W.Phill W.

80633

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  that;s no a requirement of how it works, the planner is free to reorganise the query so long as the same set of results come out (modulo extensions like incomplete group bys)
  
  – Jasen
  Oct 19 '18 at 11:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Jasen By index hints we can force the planner to do exactly we want.
  
  – Kondybas
  Oct 19 '18 at 11:53
  

  
  
  
  

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%2f220533%2fleft-join-performance-for-where-on-secondary-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Remember how SQL works: construct a set of tuples (rows) based on the given tables and joins, then filter those based on any where clause.

Because you have a where condition on the "right" table, your outer join is effectively converted into an inner join.

To retain the "outer-ness" of the join, put the filter condition into the join construct, rather than in the where clause:

select * 

from      user u 

left join gender g 

     on  u.genderId = g.id 

     and g.show = 1 

[where ...] 

share|improve this answer

answered Oct 19 '18 at 11:18

Phill W.Phill W.

80633

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  that;s no a requirement of how it works, the planner is free to reorganise the query so long as the same set of results come out (modulo extensions like incomplete group bys)
  
  – Jasen
  Oct 19 '18 at 11:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Jasen By index hints we can force the planner to do exactly we want.
  
  – Kondybas
  Oct 19 '18 at 11:53
  

  
  
  
  

add a comment | 

0

Remember how SQL works: construct a set of tuples (rows) based on the given tables and joins, then filter those based on any where clause.

Because you have a where condition on the "right" table, your outer join is effectively converted into an inner join.

To retain the "outer-ness" of the join, put the filter condition into the join construct, rather than in the where clause:

select * 

from      user u 

left join gender g 

     on  u.genderId = g.id 

     and g.show = 1 

[where ...] 

share|improve this answer

answered Oct 19 '18 at 11:18

Phill W.Phill W.

80633

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  that;s no a requirement of how it works, the planner is free to reorganise the query so long as the same set of results come out (modulo extensions like incomplete group bys)
  
  – Jasen
  Oct 19 '18 at 11:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Jasen By index hints we can force the planner to do exactly we want.
  
  – Kondybas
  Oct 19 '18 at 11:53
  

  
  
  
  

add a comment | 

Remember how SQL works: construct a set of tuples (rows) based on the given tables and joins, then filter those based on any where clause.

Because you have a where condition on the "right" table, your outer join is effectively converted into an inner join.

To retain the "outer-ness" of the join, put the filter condition into the join construct, rather than in the where clause:

select * 

from      user u 

left join gender g 

     on  u.genderId = g.id 

     and g.show = 1 

[where ...] 

share|improve this answer

answered Oct 19 '18 at 11:18

Phill W.Phill W.

80633

select * 

from      user u 

left join gender g 

     on  u.genderId = g.id 

     and g.show = 1 

[where ...] 

share|improve this answer

answered Oct 19 '18 at 11:18

Phill W.Phill W.

80633

answered Oct 19 '18 at 11:18

Phill W.Phill W.

80633

answered Oct 19 '18 at 11:18

Phill W.Phill W.

80633