What is mysql left link query? _ MySQL-mysql tutorial

What is mysql left link query?

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 the left join query, the LEFT table (posts table) should be the primary table, this produces a fundamental contradiction. MySQL handles this problem because in our WHERE section, the query conditions are filtered based on the fields in the categories table, the categories table has an appropriate index. Therefore, using the categories table as the primary table during query is more conducive to narrowing down 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.