Re: Create index for Join some table in MySQL

[Øystein Grøvlen]

Hi,

You should have indexes on all columns used in ON-conditions for INNER JOINS and on all columns of right-hand side table in ON-conditions of LEFT JOINS.

In addition you should have an index on vtiger_crmentity.deleted if the condition is selective (selects less than 20% of the rows).

In general, depending on the selected join order, it may be useful to have an index on the columns of the ORDER BY clause. However, in your case, the ORDER BY clause does not make much sense since the query will return only one row.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway