For this query
JOIN table_b USING (id)
WHERE table_b.column = 1
The optimal way is execute it is
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
- For each of those rows,
table_a. (Note that
LEFT JOIN is being used;
LEFT JOIN is a different story.)
- To reach into
table_a, an index starting with
id is needed. (Note:
table_a.id = table_b.id.)
So far, we have
a: INDEX(id) -- though it probably exists as PRIMARY KEY(id)
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.
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.
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
but often I add a composite index with both that can improve efficiency of queries like this...
As I said, your example does not exemplify the question. So, I'll try to answer "When should I use a composite index"? There are many cases (see the links); I'll give you a simple case.
WHERE x = 1
AND y > 2
The relevant characteristics are:
y are in the same table. (Can't build an index across two tables.)
AND is used. (
OR can't be optimized.)
- One of the tests is with
=. (Composite won't help if both are ranges.)
y is a "range" (examples:
y LIKE 'm%',
y BETWEEN ... AND ...).
The general rule is:
- Put all the
= columns first (
x in my example)
- Put one range column last (
That is, you must order it
WHERE x = 1 AND y = 2 (both
=), it does not matter whether you have
Another tidbit: With
PRIMARY KEY column(s) are implicitly tacked onto each secondary key. Hence, your
INDEX(column) is the same as
INDEX(column, id). But this fact does not play a role in this discussion.
I realize that I am disagreeing with other Answers here (and elsewhere), but I stand my ground.