This is a pretty simple question, however I'm having trouble finding a straight answer for it on the internet.

Let's say I have two tables:

article - id, some other properties
localisation - id, articleId, locale, title, content

and 1 article has many localisations, and theres an index on locale and we want to filter by locale.

My question is, does querying by article and joining on localisation with a where clause, like this:

SELECT * FROM article AS a JOIN localisation AS l ON a.id = l.articleId WHERE l.locale = 5;

does benefit the same from the locale index as querying by the reverse:

SELECT * FROM localisation AS l JOIN article AS a ON l.articleId = a.id WHERE l.locale = 5;

Or do I need to do the latter to make proper use of my index? Assuming the cardinality is correct of course.

CodePudding user response：

By default, the order you specify tables in your query isn't necessarily the order they will be joined.

Inner join is commutative. That is, A JOIN B and B JOIN A produce the same result.

MySQL's optimizer knows this fact, and it can reorder the tables if it estimates it would be a less expensive query if it joined the tables in the opposite order to that which you listed them in your query. You can specify an optimizer hint to prevent it from reordering tables, but by default this behavior is enabled.

Using EXPLAIN will tell you which table order the optimizer prefers use for a given query. There may be edge cases where the optimizer chooses something you didn't expect. Some of the optimizer's estimate depends on the frequency of data values in your table, so you should test in your own environment.

P.S.: I expect this query would probably benefit from a compound index on the pair of columns: (locale, articleId).