How to create an index on a join table in MySQL

This article introduces how to create an index on a join table that associates two tables in MySQL. I share it for your reference. Let's take a look at the detailed introduction:

Problem Introduction

When creating a database index, you can choose a single-column index or a composite index.

In the following situation, the user table (user) and the 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 association table?

There are four options for this table:

• Create a single column index idx_user for user_uuid
• Create a single column index idx_dept for user_dept
• Create a composite index idx_user_dept, that is (user_uuid, dept_uuid)
• Create a composite index idx_dept_user, that is (dept_uuid, user_uuid)
  

There are four situations for querying related tables:

-- 1. Check the department to which a person belongs using the AND method 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";
-- 2. Use join method to check the department to which personnel belong 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";
-- 3. Use the AND method to check personnel by department 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";
-- 4. Use join method to check the personnel in the department 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. Personnel check department using the and method

1.1 The associated table has no index

1.2 Single index Idx_dept

1.3 Single Index Idx_user

1.4 Composite Index Idx_dept_user

1.5 Composite Index Idx_user_dept

1.6 All built

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

2.1 The associated table has no index

2.2 Single Index Idx_dept

2.3 Single Index Idx_user

2.4 Composite Index Idx_dept_user

2.5 Composite Index Idx_user_dept

2.6 All are built

3. Departments use the AND method to check personnel

3.1 The associated table has no index

3.2 Single Index Idx_dept

3.3 Single Index Idx_user

3.4 Composite Index Idx_dept_user

3.5 Composite Index Idx_user_dept

3.6 All are built

4. Departments use join to check their personnel

4.1 No index on the associated table

4.2 Single Index Idx_dept

4.3 Single Index Idx_user

4.4 Composite Index Idx_dept_user

4.5 Composite Index Idx_user_dept

4.6 All are built

in conclusion

The following conclusions can be drawn from the actual test results above: It is optimal to create single-column indexes idx_user and idx_dept for user_uuid and dept_uuid respectively for this association table.

The index idx_user is applicable to querying the department of a person through the person ID; the index idx_dept is applicable to querying the persons under the department through the department.

other

Test Data

Test.sql

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.