CODEWITHSUNDEEP
mysqldatabaseoptimizationindexing
kisung Tae
kisung Tae

Reputation: 237

How composite index and left join work in MySql?

I have two tables as per below

enter image description here

I have composite index on both tables as per below:

I would like to execute the following query

select * from users u
left join address adr
on u.id = adr.user_id
where u.login_type_id = 1
and u.langauge_id = 1
and u.enabled = true
and adr.city_id in (1,2,3,4,5)
and adr.enabled = true
order by u.level

when I execute the query, I am expecting MySQL to use the composite index on both tables since all the fields in where clauses are indexed. But MySql uses the address table as base and only used the primary key of users to join.

Does MySql ignore a composite index when joining two tables?

Can you guys give me an idea of why MySql does not use composite index on both tables?

To make the query fastest, which fields should I create indexes on, please?

Thank you.

Explain SQL enter image description here

Please note that actual database is different from the database in the question but the relationship and index are the same.

Upvotes: 1

Views: 1260

Answers (1)

Rick James
Rick James

Reputation: 142528

  • When you have constraints (see WHERE) on the 'right' table of a LEFT JOIN; the Optimizer will turn it into a regular JOIN. This allows it to rearrange the tables.

  • The PRIMARY KEY is usually the optimal way to JOIN to a table.

  • A "covering" INDEX is one that has all the columns needed for the query. Since you specified SELECT *, you are asking for full_name, which is missing from your index, therefore it is not "covering".

  • The order of the columns in an index may be very important. (But not in your example.) This could lead to not using a composite index, and perhaps even a covering index.

  • Generally, it is best to put columns tested by = first in a composite index.

  • If it is not a covering index, the Optimizer will stop at the first "range" column in a composite index. (Not relevant in your example.)

  • IN(1,2,3,4) might be optimized like = or like a range, depending on the phase of the moon.

  • Cardinality of the components of a composite index does not matter when arranging the columns.

  • If the Optimizer thinks that it needs to touch more than something like 20% of an index, it will shun the index and use a table scan.

More: http://mysql.rjweb.org/doc.php/index_cookbook_mysql and http://mysql.rjweb.org/doc.php/index1

Upvotes: 2

Related Questions