How to create an index for the connected tables associated with two tables in MySQL

How to create an index for the connected tables associated with two tables in MySQL

This article describes how to create an index for the connected tables associated with the two tables in MySQL. The following describes how to create an index for your reference:

Problem description

Create a database index. You can select a single column index or create a combined index.

In this case, the user table and the Department table (dept) are connected through the department user Association Table (deptuser), as shown in:

Relationship between tables

The question is, how can I create an index in this join table?

For this table, there are four options:

• 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)
  

You can query associated tables in the following situations:

-- 1. Check the Department 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"; -- 2. query the Department and use the join method to 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 and to 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 the join method to 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 and verification

I. Use and to check the Department

1.1 The associated table has no index.

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. Check the Department by join

2.1 The associated table has no index.

2.2 single index Idx_dept

2.3 single index Idx_user

2.4 combined index Idx_dept_user

2.5 combined index Idx_user_dept

2.6 All are created

3. Department investigators and

3.1 The associated 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. Department query personnel using join

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 All are created

Conclusion

Based on the above test results, we can draw the following conclusion: for the association table, the index idx_user and idx_dept are optimal for user_uuid and dept_uuid.

The index idx_user applies to the department where the person is located, and the index idx_dept applies to the personnel under the department.

Others

Test Data

Test. SQL

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.