What is MySQL left link query how to use?

MySQL left connection query is a way to connect the query, the following is to introduce you to the MySQL left connection query some of the questions to talk about, if you are interested, may wish to see.

The main Table I'm talking about here refers to which table MySQL is querying in the connection query. For example, in the MySQL left-link query, generally speaking, the left table is the main table, but this is just experience, often empirical is unreliable, in order to illustrate the problem, first of all, build two demo table categories and posts:

 
 

  
  

  
CREATE TABLE IF not EXISTS ' categories ' (
  
  

  
' ID ' int (a) unsigned not NULL auto_increment,
  
  

  
' Name ' varchar not NULL,
  
  

  
' Created ' datetime not NULL,
  
  

  
PRIMARY KEY (' id '),
  
  

  
KEY ' name ' (' name ')
  
  

  
);
  
  

  

  
  

  
CREATE TABLE IF not EXISTS ' posts ' (
  
  

  
' ID ' int (a) unsigned not NULL auto_increment,
  
  

  
' category_id ' int (a) unsigned not NULL,
  
  

  
' title ' varchar Not NULL,
  
  

  
' content ' varchar not NULL,
  
  

  
' Created ' datetime not NULL,
  
  

  
PRIMARY KEY (' id '),
  
  

  
KEY ' category_id ' (' category_id '),
  
  

  
KEY ' created ' (' created '),
  
  

  
KEY ' category_id_created ' (' category_id ', ' created ')
  
  

  
);
 
 

First of all, notice the index of each table, later will use, remember to insert a bit of test data, not too much, but how to get more than two lines, and then perform 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 results are as follows:

 
  
  

  
   
   
Table Key Extra
  
   
   
Categories name Using where; Using temporary; Using Filesort
  
   
   
Posts category_id
 
  
  

In the result of the explain of the join query, the first row represents the table that is the primary table. So in this query categories is the primary table, and in our experience, the left table (posts table) should be the primary table in the left-hand join query, which creates a fundamental paradox, because in our where part, MySQL The query criteria are filtered according to the fields of the Categories table, and the Categories table has exactly the right index, so it is more advantageous to reduce the result set when querying the Categories table as the primary table.

The using temporary in the explain result; Using Filesort is why, why is created or category_id_created index invalid? This is because the primary table is the Categories table, from the table is the posts table, and we use the fields from the table to order by, This is usually not a good choice, and it's best to change to a main table field. But a lot of times can not change, then there is no recruit.

Let's look at a more bizarre example:

 
  
  

  
   
   
EXPLAIN SELECT *
  
   
   
From posts
  
   
   
Left JOIN categories on posts.category_id = Categories.id
  
   
   
WHERE categories.id = ' An already existing ID '
  
   
   
ORDER BY posts.created DESC
 
  
  

In this case the posts table is still from the table, but the results sorted from the table do not appear in the file sort and temp table, because the categories.id is already defined, so the primary table is equivalent to a constant table with only one row of data, from the table based on the category_id_ The created index will naturally get sorted results when connected. But to put it another way, since categories.id are OK, that kind of demand, we generally will not use the left JOIN query, and will be divided into two separate queries to retrieve categories and posts.

Subjectively, once the master table is mistaken, it is possible to adjust the index without efficient SQL, so when writing SQL, for example, when writing a MySQL left-join query, if you want the left table to be the primary table, make sure that the query conditions in the WHERE statement use as much of the left table field as possible, and then, once the master table is determined, It's also best to go through the Main table field only.

Note: In most cases, using a sort from table field is inefficient, and my original example misled you and corrected it.