MySQL left join query Experience Summary

MySQL left join query is a method in connection query. The following describes some questions about MySQL left join query. If you are interested, take a look.

The primary table I mentioned here refers to the table in the connection query where MySQL mainly queries. For example, in MySQL left join queries, the left table is generally the primary table, but this is just an empirical statement. In many cases, experience is unreliable. To illustrate the problem, let's give an example first, create two tables for Demonstration: categories and posts:

 

  
CREATE TABLE IF NOT EXISTS `categories` (  
  
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  
`name` varchar(15) NOT NULL,  
  
`created` datetime NOT NULL,  
  
PRIMARY KEY (`id`),  
  
KEY `name` (`name`)  
  
);  
  
 
  
CREATE TABLE IF NOT EXISTS `posts` (  
  
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  
`category_id` int(10) unsigned NOT NULL,  
  
`title` varchar(100) NOT NULL,  
  
`content` varchar(200) NOT NULL,  
  
`created` datetime NOT NULL,  
  
PRIMARY KEY (`id`),  
  
KEY `category_id` (`category_id`),  
  
KEY `created` (`created`),  
  
KEY `category_id_created` (`category_id`, `created`)  
  
);  
  
 
 

Pay attention to the index situation of each table, which will be used later. Remember to insert a little test data without too much, but you have to have more than two rows, and then execute the following

 

  
SQL：  
  
 
  
EXPLAIN SELECT *  
  
FROM posts  
  
LEFT JOIN categories ON posts.category_id = categories.id  
  
WHERE categories.name LIKE 'foobar%'  
  
ORDER BY posts.created DESC  
  
 
 

The result is as follows:

 

  
table      key         Extra  
  
categories name        Using where; Using temporary; Using filesort  
  
posts      category_id  
  
 
 

In the explain results of the join query, the table in the first row is the primary table. Therefore, in this query, categories is the primary table. In our experience, in left join queries, the LEFT table posts should be the primary table, which produces a fundamental contradiction, the reason why MySQL does this is that in our WHERE section, the query conditions are filtered Based on the fields in the categories table, and the categories table has a suitable index, therefore, using the categories table as the primary table during query is more conducive to reducing the result set.

Why is the Using temporary; Using filesort in the explain result invalid? This is because the main table is a categories table, the slave table is a posts table, and we use the field from the table to go to order by. This is usually not a good choice. It is best to change it to the main table field. However, if it cannot be changed in many cases, it will be useless.

Let's look at a strange example:

 

  
Explain select *
  
FROM posts
  
Left join categories ON posts. category_id = categories. id
  
WHERE categories. id = 'an existing id'
  
Order by posts. created DESC
  
 
 

In this example, the posts table is still a slave table, but no File Sorting or temporary table is displayed in the result of sorting by slave table, because categories has been determined. id. Therefore, the primary table is equivalent to a constant table with only one row of data. The sorted results are obtained from the table when the table is connected according to the category_id_created index. But from another perspective, since categories. IDs are all determined, so we generally do not use left join queries, but will be divided into two independent queries to retrieve categories and posts.

Subjective, once the primary table is wrong, how to adjust the index will not be able to get efficient SQL statements, so when writing SQL statements, such as when writing MySQL left join queries, if you want the left table to be a master table, make sure that the query conditions in the WHERE statement use as many left table fields as possible. Once the master table is determined, it is also best to use the primary table field to go to order.

Note: In most cases, sorting from table fields is inefficient. My first example misled everyone and has corrected it.

 

How to modify the table structure in mysql

MySQL memory table heap usage Summary

MySQL connection Query

How to Implement MySQL multi-Table insertion

Two tips for MySQL table repair