Shulou(Shulou.com)05/31 Report--

How to use left link query in mysql? for this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

The master table I'm talking about here refers to which table is used by MySQL to query in the join query. For example, in the left join query of MySQL, generally speaking, the left table is the main table, but this is only a rule of thumb. In many cases, empiricism is unreliable. To illustrate the problem, let's take an example and create two demonstration tables, 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`))

First pay attention to the index of each table, which will be used later. Remember to insert a little test data at random, not too much, but it takes more than two rows anyway, 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 results are as follows:

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

In the results of the explain of the join query, the table represented by the first row is the main table. So in this query, categories is the main table, while in our experience, in the LEFT JOIN query, the left table (posts table) should be the main table, which creates a fundamental contradiction. MySQL deals with this because in our WHERE part, the query conditions are filtered according to the fields of the categories table, and the categories table happens to have an appropriate index, so it is more helpful to reduce the result set when querying the categories table as the main table.

So why is the Using temporary; Using filesort in the explain result, and why is the created or category_id_created index invalid? This is because the master table is the categories table and the slave table is the posts table, and we use the fields of the slave table to ORDER BY, which is usually not a good choice and is best changed to the master table field. But most of the time, it can't be changed, so there's no confession.

Look at a more bizarre 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 according to the results of sorting according to the slave table, there is no file sorting and temporary table, this is because the categories.id has been determined, so the master table is equivalent to a constant table with only one row of data, and the sorted results can be naturally obtained at the same time when the category_id_created index is joined from the table. But from another point of view, now that categories.id is certain, we will no longer use LEFT JOIN queries for requirements like this, but will split them into two separate queries to retrieve categories and posts.

Subjectively, once you get the main table wrong, you may not get an efficient SQL by adjusting the index, so when writing SQL, for example, when writing a MySQL left join query, if you want the left table to be the main table, then make sure that the query conditions in the WHERE statement use the left table fields as much as possible, and then, once the main table is determined, it is best to ORDER BY only through the main table field.

This is the answer to the question about how to use the left link in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.