Separate Join clause in a Composite Index

Question

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) ```

Accepted Answer

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)`.

Separate Join clause in a Composite Index

Solution

Problem