mysql-Correlation Query

Three ways to do MySQL related queries:

SELECT * from film JOIN film_actor on (film.film_id = film_actor.film_id);
SELECT * from film JOIN film_actor USING (film_id); --You can use the using () when the two field names are the same for the associated table
SELECT * from film, film_actor WHERE film.film_id = film_actor.film_id;

Refine the associated query:

• Make sure that there is an index on the column in the on or using clause. The correlation order is taken into account when creating the index. In general, you only need to create an index on the corresponding column of the second table in the association order, unless there are other reasons. For example, when table film and table Film_actor are associated with columns film_id. If the optimizer's association order is film_actor, film then it does not need to be indexed on the film_id column of the Film_actor table.
• Make sure that any expression in group by and order by involves only the columns in one table, so that MySQL can use the index to optimize the process.
• When upgrading MySQL, you need to be aware of the associated syntax, operator precedence, and other areas that may change. Because the previously ubiquitous places may become Cartesian, different types of associations can produce different results, and so on.

Reference:

[1] Baron Schwartz, Ninghai Yuanhao and so on, "high-performance MySQL" (3rd edition); electronics Industry Press, 2013

[2] Blog, http://www.jb51.net/article/68442.htm

mysql-Correlation Query