Mysql-join query, mysql-join

Mysql-join query, mysql-join
Three methods of MySQL association query:

SELECT * FROM film JOIN film_actor ON (film. film_id = film_actor.film_id );
SELECT * FROM film JOIN film_actor USING (film_id); -- when the field names of the two tables to be joined are the same, you can use USING ()
SELECT * FROM film, film_actor WHERE film. film_id = film_actor.film_id;

Optimize association query:

• Make sure that the column in The ON or USING clause has an index. When creating an index, you must consider the order of association. In general, unless for other reasons, you only need to create an index on the corresponding column of the second table in the association sequence. For example, when the film and film_actor columns are associated with film_id. If the optimizer's association sequence is film_actor and film, you do not need to add an index to the film_id column of the film_actor table.
• Make sure that any expressions in group by and order by only involve columns in a table, so that MySQL can use indexes to optimize the process.
• When upgrading MySQL, note that the associated syntax, operator priority, and other issues may change. In the past, the common associations may become cartesian products, and different types of associations may generate different results.

Refer:

[1] Baron Schwartz; translated by Ninghai yuan; high-performance MySQL (version 3rd); Electronic Industry Press, 2013

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