Would having a Composite Index be beneficial for something like this:
SELECT * FROM a INNER JOIN b ON(a.id=b.id)
INNER JOIN c ON(a.bar=c.id)
INNER JOIN d ON(a.foo=d.id)
Index would be:
(a.id, a.bar, a.foo)
Only the leading edge of the index would be used (a.id
), so only the INNER JOIN
to b
would benefit from the index... so the additional columns in the index (a.bar
and a.foo
) are not beneficial in the sample query posted.
From the MySql documentation:
MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the
SELECT
statements shown here:SELECT * FROM tbl_name WHERE col1=val1; SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; SELECT * FROM tbl_name WHERE col2=val2; SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
If an index exists on
(col1, col2, col3)
, only the first two queries use the index. The third and fourth queries do involve indexed columns, but(col2)
and(col2, col3)
are not leftmost prefixes of(col1, col2, col3)
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With