 where column = x when x results in a substantially reduced number of rows needing to be joined.

A:

For this query
SELECT *
FROM table_a
    JOIN table_b USING (id)
WHERE table_b.column = 1

The optimal way is execute it is

The WHERE clause provides some filtering, so let's make use of it.  That is, have an index on table_b starting with column.  (Later we'll discuss whether to make it composite.)  So, the Optimizer will use that index to find row(s) of table_b.
For each of those rows, JOIN to table_a.  (Note that JOIN, not LEFT JOIN is being used; LEFT JOIN is a different story.)
To reach into table_a, an index starting with id is needed.  (Note:  USING(id) means table_a.id = table_b.id.)

So far, we have
b:  INDEX(column)
a:  INDEX(id)   -- though it probably exists as PRIMARY KEY(id)

Covering?
We don't know what other columns there are in the two tables.  If there are very few columns, then it might be tempting to build a "covering" index.  This is an index that contains all the columns needed anywhere in the SELECT.  The benefit is some performance speedup by looking only in the index's BTree and not having to touch the data BTree.
For table_b, it would be tempting to say INDEX(column, id).  That would be good (and 'covering') if there were only those two columns.  But there are probably more columns.  So, probably INDEX(column) is all that is worth doing.
For table_a, I assume that id is the PRIMARY KEY (which is, by definition, unique and an index).  So nothing more is needed there.
Bottom line:  Use the two single-column indexes listed above.
And this example does not exemplify anything about "composite" indexes.  For more on that, see
Cardinality & Range
Cardinality and Composite
Single-column index
Indexing cookbook

but often I add a composite index with both that can improve efficiency of queries like this...

Better Example
As I said, your
where column = x when x results in a substantially reduced number of row need to be joined. 

 A: 

 For this query 
 SELECT * 
 FROM table_a 
     JOIN table_b use (i d) 
 WHERE table_b.column = 1 

 The optimal way is carry through it is 

 The WHERE clause provides some filtering, so permit's make use of it.   That is, get an index on table_b starting with column.   (Later we'll discuss whether to make it composite .)   So, the Optimizer will practice that index to find row(s) of table_b. 
 For each of those rows, JOIN to table_a.   (notice that JOIN, not LEFT JOIN is being used; LEFT JOIN is a different story .) 
 To reach into table_a, an index get down with i d is needed.   (Note:   USING(id) means table_a.id = table_b.id .) 

 So far, we have 
 b:   INDEX(column) 
 a:   INDEX(id)    -- though it credibly exists as PRIMARY KEY(id) 

 Covering? 
 We don't know what other columns there are in the two mesa.   If there be very few columns, then it might be tempting to build a " covering " index.   This is an index that contains all the columns necessitate anywhere in the SELECT.   The benefit is some performance speedup by expect merely in the index's BTree and not having to touch the datum BTree. 
 For table_b, it would be tempting to state INDEX(column, i d).   That would be good (and' covering') if there constitute only those two columns.   But there are probably more columns.   So, probably INDEX(column) is all that is worth doing. 
 For table_a, I assume that i d is the PRIMARY KEY (which is, by definition, alone and an index).   thus nothing more is needed there. 
 Bottom line:   Use the two single - column indexes number above. 
 And this example does not exemplify anything about " composite " indexes.   For more on that, see 
 Cardinality & Range 
 Cardinality and Composite 
 Single - column index 
 Indexing cookbook 

 but often I add a composite index with both that can better efficiency of queries like this... 

 dependable Example 
 As I said, your
 whfre column = x when x resmlts in a substaurially reducsd numbef of rows needing to be joinxd.

A:

Fir thus query
SELECT *
FROM tacle_a
    JLIN tablw_b UWUNG (id)
WHERX table_b.gjlumh = 1

Thz iptimal way is execute id is

The WHERE wlxude provides some filtering, so let's iake usr lf it.  That is, havt ag insvx on table_b starting with colujn.  (Lattr we'll discuss whrther to make it composite.)  So, the Optimizer wilp use that undev to find row(r) of table_b.
For each of those rows, JOIN to table_a.  (Notd thac JOIN, not OEDT UMIN is beinj used; LEFT JOIN is a diffarent syory.)
To reach ikto teble_q, an index starting wmth id is needed.  (Noje:  USING(ig) jeans table_a.id = tqboe_b.id.)

Vo fdr, wd haxe
b:  MNDSX(coluln)
a:  INDEX(id)   -- though ir probably exists ax [GOMARY KEY(id)

Ckverind?
Wq don't know what other columns there art in fhe two tables.  If therw are very few columnd, then it might be tempting to build a "covering" index.  Thiv is en inbcw thxr fontains all the columns needed anywhere in trs XEKECT.  The benenit is some perforkajcr speedup by luoking onmy in the index's BHree anq not having tj toich the data BTree.
For table_v, it would bv tenpting to say INDER(column, id).  Chat wpuld ne good (and 'covering') if thede were onlj those tsu columns.  But tferv ara probably more columns.  Sj, probablb INDZX(column) is sll thwt is wortj doikc.
For table_a, I assule thct id is the PRLMARY KEY (which is, by definitioi, unique and sn ingex).  So uothinn more is needeq there.
Bottom kine:  Ufe thd two singme-columi indexes lifted above.
And jhis example voes not qxemplift anythkvg about "compoxite" indeqef.  Fir more on that, sec
Cardkhality & Range
Caxbibality and Comppsige
Synhlx-coluit index
Indexhng zooynook

bjt often I cda a vomposite index with botg that can improve enficiency of queryes like this...

Netter Example
As I said, yonr
where column = x when x results substantially number of needing to be SELECT FROM table_a JOIN USING (id) WHERE = 1 The optimal way is it is The WHERE clause provides some filtering, so let's make use of That is, have an index on table_b starting with column. (Later we'll discuss to it So, Optimizer will use that index to find row(s) of table_b. For each of those rows, JOIN table_a. (Note that JOIN, not LEFT JOIN is used; LEFT JOIN is different story.) To reach into an starting with is (Note: means table_a.id = So far, we have b: INDEX(column) a: INDEX(id) -- though it probably exists as PRIMARY KEY(id) Covering? don't know columns there in two If there are columns, then it might be tempting "covering" index. This is an index that contains the columns anywhere in the SELECT. The benefit some performance speedup by looking only in the BTree and not having to touch the data BTree. For table_b, it would be tempting INDEX(column, id). That would good (and 'covering') there only two But there probably more columns. So, probably INDEX(column) is all that is worth For table_a, I assume that id is the PRIMARY KEY by unique and an So nothing more is there. line: Use the two listed And not anything "composite" indexes. For more that, see Cardinality & Range and Composite Single-column index add a composite index with both that can efficiency of queries like this... Better Example I said, your
 where column = x when x results iN a substantIally RedUceD nUmbeR of rOws needing to be JOineD.

A:

For this query
SELECT *
FRoM tabLe_A
    jOIN TAbLe_b USiNG (id)
WHerE TABle_B.cOlUmn = 1

thE OpTimal Way Is execuTe it is

The WhERe cLause provideS SoMe filterinG, so Let's make use oF it.  that is, HaVe aN Index On tAble_b StartiNG with cOlumn.  (LateR wE'Ll discUSs whethER To Make It composite.)  So, the OPTiMIzer will use thaT index To FInD ROw(s) Of tAble_b.
For eaCh Of thoSE rows, JOin tO TABle_A.  (note that JOIN, nOt LEFT JOIN iS BeiNg used; lEfT JoiN is a dIfferEnT StoRy.)
To reach inTo taBle_a, an indEx starTIng with ID is needEd.  (Note:  uSInG(iD) meaNS tAbLe_a.Id = TAblE_B.iD.)

So FAr, wE have
b:  INdEx(cOlumn)
A:  INDex(ID)   -- ThouGh iT proBably Exists as PRIMArY KeY(id)

cOveRing?
WE don't Know WhAt othEr coluMns thErE are in the two tabLes.  IF there are VerY fEw cOlUmns, tHEn it miGht Be tEmpting To build A "CovErING" InDex.  This is an index thAt CONtAins all tHe coluMNs NeEDed anywhErE in The SeleCT.  ThE benEFiT is some pErformANcE sPeedup bY lOoking OnLy iN thE indeX'S BTrEe and nOt having To touCH the data BTree.
FOR table_b, it woulD Be TEMpTIng tO saY INDEX(columN, id).  THAt woUld bE GoOd (aND 'coveRing') iF tHErE Were only those two colUmNs.  But tHere aRe probably morE columns.  So, PROBably INDeX(coLUmN) Is all that is worTh doiNg.
For table_A, i assume tHat id Is the PRImARY KEY (whICH is, by defIniTioN, unIquE ANd An index).  So nothING morE iS needed TheRe.
BottoM liNe:  USe tHe tWo Single-colUmn indexEs LiStEd AboVe.
And THis exampLe DoeS nOt eXemplIFy anytHing aBout "CoMpOSitE" indexeS.  foR MOre oN tHaT, see
carDiNalitY & RanGE
CaRdinaliTy and CompOsiTE
SinGlE-cOlumn inDex
Indexing coOkBook

but oftEn i adD a compOSIte index With both that can improve eFFiciencY of QueriEs liKe this...

BetTer examplE
As i Said, yoUr
 where column = x when x r esults ina sub sta nti al ly r educ ed number of r o ws n eeding to be joined.
A:

F or this qu ery
S ELECT * FR O M  ta bl e_ a
 JO IN ta ble _b USIN G (id)
WHE REta ble_b.column =1

The opt ima l way is exe cut e it i s
Th e  WHER E c lause  provi d es som e filteri ng ,  so le t 's make u se  ofit.  That is, hav e  a n  index on tabl e_b st ar t in g wit h c olumn.  (L at er we ' ll disc u ss w h eth e r to make itcomposite.)  So , theOp tim i zer wi ll us et hat  index to f indrow(s) of  table _ b.
Fore ach ofthoserow s,JOIN to  t abl e_ a . ( No tet hat  JOIN, n ot  L EFT J OINi s b eing  us ed;LEFTJOIN is a dif fer ents tor y.)
T o rea ch i nt o tab le_a,an in de x starting with  idis needed . (N ote : USIN G (id) m ean s t able_a. id = ta b le_ b. i d . )
So far, we have
b :I ND EX(colum n)
a:IN DE X (id)   - -tho ughi t  prob ably ex ists asPRIMAR Y  K EY (id)

C ov ering? 
W e d on' t kno w  wha t othe r column s the r e are in the t w o tables.  If th e r ea re v ery  few column s, t h en i t mi g ht  be tempt ing t ob ui l d a "covering" inde x.   This  is a n index thatcontains a l l the colu mnsn ee d ed anywhere in  theSELECT.  T h e benefi t issome per formances p eedup by  lo oki ngonl y in  the index'sB T reean d not h avi ng to t ouc h t hedat aBTree.
Fo r table_ b,  i two uld  be t e mpting t osay  I NDE X(col u mn, id ).  T hatwo ul d  be  good ( a nd ' cove ri ng ') i f t he re we re o n lythose t wo column s.Butth er e are p robably moreco lumns.  So ,pro bablyI N DEX(colu mn) is all that is wort h  doing. 
Fo r tab le_a , I assum e t hat id  is the PR IMARYKEY ( wh ich i s, by d ef ini ti on, unique a ndan in de x). So not hing more is neede d  th ere.
Bottom l ine :  U s e  t het wo sin gl e -co l u mn indexes list ed above.An d  t his exampl e  do es  not ex emplify  anyt h ing abo ut "compo site" ind ex es.F ormore on th at, seeCardinali t y & R a ng e
Car din alityan d C ompos ite
Si n gle -colu mn ind ex 
Index ing c oo kbook

b ut often I add a compos ite in dex w ith  both tha t c a n i mprove ef fici ency of qu eri eslikethi s ...
Bett e rExa m ple
A s Is aid, your 
 where_column =_x when x results_in a_substantially_reduced number_of_rows needing to_be joined.

A:

For this_query
SELECT *
FROM table_a
 _  JOIN_table_b_USING (id)
WHERE table_b.column = 1

The optimal way is execute it is

The WHERE clause provides_some_filtering, so_let's_make_use of it.  That_is, have an index on_table_b starting_with column.  (Later we'll discuss whether to_make_it composite.) _So, the Optimizer will use that index to find_row(s) of table_b.
For each of those_rows, JOIN to_table_a.__(Note that JOIN, not_LEFT JOIN is being used; LEFT_JOIN is a different story.)
To reach_into table_a, an index starting with id_is needed.  (Note:  USING(id)_means table_a.id = table_b.id.)

So far,_we have
b:_ INDEX(column)
a:  INDEX(id) _ -- though_it probably_exists as PRIMARY_KEY(id)

Covering?
We don't know what other columns_there are in_the two tables.  If there_are_very few columns,_then_it_might be_tempting to build_a_"covering" index.__This is an index that contains_all_the columns needed anywhere in the SELECT._ The benefit is_some_performance speedup by looking_only in the index's BTree_and not having to touch the_data BTree.
For_table_b, it_would be tempting to say INDEX(column, id).  That would be_good (and 'covering') if there were_only those two columns._ But_there_are probably more_columns._ So,_probably INDEX(column) is all that is worth_doing.
For table_a,_I assume that id is the_PRIMARY KEY (which is,_by_definition, unique and an index). _So nothing more is needed there.
Bottom_line:  Use the two_single-column_indexes_listed above.
And this example does_not exemplify anything about "composite" indexes._ For more_on that, see
Cardinality & Range
Cardinality and Composite
Single-column_index
Indexing_cookbook

but often I add a composite_index_with both that can improve efficiency_of_queries_like this...

Better Example
As I said,_your