Detailed graphic and text explanation of how to create an index for a join table related to two tables in MySQL

This article introduces to you through pictures and text the relevant information on how to create an index for the connection table associated with two tables in MySQL. The introduction in the article is very detailed and has certain reference and learning value for everyone. Friends who need it can read it together. Take a look.

Problem Introduction

To create a database index, you can choose a single column index or create a combined index.

In the following situation, the user table (user) and department table (dept) are connected through the department user association table (deptuser), as shown in the following figure:

Relationships between tables

The question is, how to create an index in this related table?

For this table, there are four options:

• Create a single column index idx_user for user_uuid

• For user_dept Create a single column index idx_dept

• Create a combined index idx_user_dept, that is (user_uuid, dept_uuid)

• ##Create a combined index idx_dept_user, that is (dept_uuid, user_uuid)

  
  

There are four situations for querying related tables:

-- 一、人员查所属部门用and方式
EXPLAIN SELECT d.dept_name,u.* FROM org_dept d,org_user u,org_dept_user duser WHERE u.user_uuid=duser.user_uuid AND d.dept_uuid=duser.dept_uuid AND u.user_code="dev1";
-- 二、人员查所属部门用join方式
EXPLAIN SELECT d.dept_name,u.* FROM org_user u LEFT JOIN org_dept_user du ON u.user_uuid=du.user_uuid LEFT JOIN org_dept d ON du.dept_uuid=d.dept_uuid WHERE u.user_code="dev1";
-- 三、部门查人员用and方式
EXPLAIN SELECT d.dept_name,u.* FROM org_dept d,org_user u,org_dept_user du WHERE u.user_uuid=du.user_uuid AND d.dept_uuid=du.dept_uuid AND d.dept_code="D006";
-- 四、部门查所属人员用join方式
EXPLAIN SELECT d.dept_name,u.* FROM org_dept d LEFT JOIN org_dept_user du ON d.dept_uuid=du.dept_uuid LEFT JOIN org_user u ON u.user_uuid=du.user_uuid WHERE d.dept_code="D006";

Test verification

1. Use and to check the department to which a person belongs Method

1.1 No index for related table

1.2 Single index Idx_dept

1.3 Single index Idx_user

1.4 Combined index Idx_dept_user

##1.5 Combined index Idx_user_dept

1.6 All are created

2. Use the join method to check the departments to which personnel belong

2.1 The associated table has no index

2.2 Single index Idx_dept

2.3 Single index Index Idx_user

2.4 Combined index Idx_dept_user

##2.5 Combined index Idx_user_dept

2.6 All are established

3. Department check personnel using and method

3.1 Association The table has no index

3.2 Single index Idx_dept

3.3 Single index Idx_user

3.4 Combined index Idx_dept_user

3.5 Combined index Idx_user_dept

3.6 All are created

#4. Use the join method to check the personnel belonging to the department

4.1 The associated table has no index

4.2 Single index Idx_dept

4.3 Single index Idx_user

4.4 Combined index Idx_dept_user

##4.5 Combined index Idx_user_dept

4.6 Everything is built on

Conclusion

Through the above actual test results, the following conclusions can be drawn: For this association table, it is optimal to establish single-column indexes idx_user and idx_dept respectively for user_uuid and dept_uuid.

The index idx_user is suitable for querying the department where the person is located through the personnel ID; the index idx_dept is suitable for querying the personnel belonging to the department through the department.

Related recommendations:

Analyze the combined use of aggregate functions and connection tables_MySQL

mysql implements association updates between two tables_MySQL 、

Detailed introduction to table partitioning in MySQL

The above is the detailed content of Detailed graphic and text explanation of how to create an index for a join table related to two tables in MySQL. For more information, please follow other related articles on the PHP Chinese website!