[mysql series] In MySQL query optimization, there is a "small table drives large table query". According to this idea, where can we optimize mysql query? Join query, in query, exists query, etc.

【mysqlSeries】InMySQL query optimizationThere is a "small table drives large table query". According to this idea, where can we optimize mysql query? Join query, in query, exists query, etc.

"Small table drives large table query"

A X B = C

It can be understood that small tables are in front and large tables are in back. That is, A is a small table and B is a large table.

In MySQL query optimization, "Small Table Driving Large Table Query" is an optimization strategy. Its core idea is to use smaller tables (or tables with smaller result sets) as driver tables to connect larger tables when performing joins (JOIN) operations. Doing so can significantly improve query efficiency, as the database system can traverse smaller tables faster and in smaller tablesDatasetFind rows that match the larger table.

Give an example

Suppose we have two tables:employees(employee form) anddepartments(Department Table).

• employeesThe table contains a large number of records, such as all the company's employee information, assuming there are 100,000 records.
• departmentsThe table contains departmental information, with a relatively small number, such as only 100 records.

If we want to query the number of employees in each department, we can use the following two SQL query methods, but they areperformanceThere may be differences on the top.

Query method 1: Small table drives large tables

SELECT d.department_name, COUNT(e.employee_id) AS employee_count 




FROM departments d 




JOIN employees e ON d.department_id = e.department_id 




GROUP BY d.department_id;

AI writing code

In this query,departmentsTable (small table) serves as the driver table because it is fromdepartmentsThe table starts traversal, for each department, it will goemployeesFind employees belonging to the department in the table (large table). becausedepartmentsThe table is small, so this query method is more efficient.

Query method 2: Large tables drive small tables (not recommended)

In theory, you can also write the query in reverse, letemployeesTables are connected as driver tablesdepartmentstable, but in this case, due toemployeesThe table is very large, which causes the database to traverse a large number of rows to finddepartmentstable matches rows, thus reducing query efficiency.

SELECT d.department_name, COUNT(e.employee_id) AS employee_count 




FROM employees e 




JOIN departments d ON e.department_id = d.department_id 




GROUP BY e.department_id;

AI writing code

Although this query is logically correct and can get the same results, it is not as efficient as the first method in terms of execution.

in conclusion

When writing SQL queries, especially in queries involving connection (JOIN) operations, understanding and applying the principle of "small tables drive large tables" can help you optimize query performance, reduce database load, and speed up query speed. In practical applications, if possible, by viewing the statistics of the table (such as the number of rows) or usingEXPLAINCommands to analyze query plans can help you determine which table is smaller and thus determine the order of querying.

Based on this idea, in the next article, we will talk about which one is better to use exists query and in query. In addition, why not recommend left join query with more than 3 tables?