Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Separate Join clause in a Composite Index

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)
like image 428
user1789469 Avatar asked Oct 31 '12 18:10

user1789469


1 Answers

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

like image 163
Michael Fredrickson Avatar answered Nov 05 '22 21:11

Michael Fredrickson