Importance of indexes in MySQL connection queries

Source: Internet
Author: User

Importance of indexes in MySQL connection queries

In MySQL, connection queries are often used to read data from multiple tables. Connection query uses the fields in the two tables that match the connection relationship to establish the association between the two tables, including the inner join, left Outer Join, right outer join, and full join. The internal Join Operation retains the records of the two tables. Therefore, the number of records in the connected table is the minimum. The full Join Operation retains all the records in the two tables, therefore, the maximum number of records is generated in the connected table. The left Outer Join retains all records in the left table, and the right Outer Join retains all records in the right table, therefore, the number of records of the final connection table is between the inner connection and the outer connection.

The following is an example of selecting a course for a student to analyze the performance of the lower left Outer Join.

First define a student table ):

 

Insert a total of 10000 student records:

Define a Student Course Selection table (student_to_class ):

Insert a Course Selection Record for the student. Each student chooses two courses with 20000 records in total:

Now we need to count the personal information of each student, including his name and the number of courses he selected. In this way, we need to use the left Outer Join. The specific SQL statement is as follows:

SELECT
A. student_id, student_name, count (*)
FROM
Student
Left join student_to_class B ON a. student_id = B. student_id
Group by a. student_id;

However, the query execution speed is very slow and it takes 75.467 s. Of course, this is also related to the fact that I am on the local machine, rather than setting up a database on the server. However, this query efficiency cannot be tolerated.

 

Next we will analyze why it is so slow:

First, use explain to view the query execution plan of this statement. We can see that ALL types are ALL, that is, full table scans are used in both student and student_to_class tables. student table () 10649 rows are scanned, and 20287 rows are scanned in student_to_class table (B), which is undoubtedly very inefficient.

In this regard, we try to add an index to the student_id field of the student_to_class table:

Then execute the query again and find that the query speed is very fast, only 0.077 s, and the query speed is improved a lot. As shown in the corresponding query execution plan, it is found that the index student_index is used when the student_to_class table is queried, so that only one row needs to be scanned, which is equivalent to one in 20 thousand, this is the key point of efficiency improvement.

Therefore, when the connection table generated during connection query is too large, we need to use indexes frequently to reduce the number of queries and improve the query efficiency.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.