Summarize and sort out the pitfalls of mysql left join query that is slow and takes a long time

This article brings you relevant knowledge about mysql, which mainly introduces the summary of the pitfalls of slow left join query and long time, including the EXPLAIN command to analyze the SELECT statement, as follows Let's take a look, I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

Problem background

Two tables and one is user table a (The primary key is int type), one is user specific information table b (the user table id field is varchar type).

Because we want to display users and user information, we need to query relatedly. However, we found that the query after left join is slow and takes too long. The user table data is about 20,000.

Problem analysis and processing

1. Use the EXPLAIN command to analyze the SELECT statement

type The field provides an important basis for judging whether the query is efficient. Through the type field, we judge whether the query is a full table scan or an index scan.

ALL: Indicates a full table scan. This type of query has the highest performance. One of the worst queries.

Generally speaking, our queries should not have ALL type queries, because such queries will have a huge disaster on the performance of the database when the amount of data is large. For example, a The query is an ALL type query, so generally speaking, you can add an index to the corresponding field to avoid this.

2. New index

Because it is found that the index of field b in table has not been created before.

alter table a add index idx_mbrID (mbrID);

Explain the analysis again

It is found that the type has changed to ref. After comparing the performance relationship of different types (

ALL < index < range ~ index_merge < ref < eq_ref < const < system

) It felt like it was OK, so I executed the query.

3. Modify the index field type to be consistent

After executing the query, I found that the execution speed was not optimized. I carefully looked at the table designed by my previous colleague and found that the index type field It was inconsistent, so I changed it from varchar to int and then queried again and found that the query speed was significantly improved.

Even if the string written in the java code before was changed to int in the database, the current test can be used normally

Summary

After solving the problem I flipped through the development manual and found that the index specification clearly mandates that the data types must be consistent during joins, and the related fields must have indexes! ! !

Recommended learning: mysql video tutorial

The above is the detailed content of Summarize and sort out the pitfalls of mysql left join query that is slow and takes a long time. For more information, please follow other related articles on the PHP Chinese website!