Shulou(Shulou.com)06/01 Report--

How to create index graph and text for join tables associated with two tables in MySQL? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Problem introduction

To create an index of the database, you can choose either a single-column index or a combined index.

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

Relationship between tables

The question is, how do you build an index in this associated table?

There are four options for this table:

Build a single-column index idx_user for user_uuid

Build a single-column index idx_dept for user_dept

Set up a composite index idx_user_dept, namely (user_uuid,dept_uuid)

Set up a composite index idx_dept_user, namely (dept_uuid,user_uuid)

There are four situations for querying associated tables:

-- first, the personnel check the department to which they belong using the and method EXPLAIN SELECT d.deptdistributor _ name _ EXPLAIN SELECT u. * FROM org_dept d _ paper orgkeeper user u _ reel orgkeeper user u _ r _ dev1 _ code = "dev1" -- second, the department to which the personnel check uses the join method EXPLAIN SELECT d.deptdistributor name EXPLAIN SELECT 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. Username code = "dev1" -- third, the department inspector uses the and method to EXPLAIN SELECT d. Deptabilnamereel u. * FROM org_dept d paper orgkeeper user u u reel orgdeptress user du WHERE u.user_uuid=du.user_uuid AND d.dept_uuid=du.dept_uuid AND d.deptdesk code = "D006". -- fourth, the personnel of the department shall use the join method EXPLAIN SELECT d.deptdistributor name EXPLAIN SELECT 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.deptinspection code = "D006"

Test verification

one。 And is used to check the departments to which they belong.

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 are built on

2. Join is used to check the departments to which the 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 on

III. Departmental inspectors use and.

3.1 the associated table has no index

3.2 single index Idx_dept

3.3 single index Idx_user

3.4 Combinatorial index Idx_dept_user

3.5 Composite Index Idx_user_dept

3.6 all are built on

4. The personnel of the department shall be checked by join.

4.1 the associated table has no index

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 on

Conclusion

Through the above actual test results, we can draw the following conclusion: for the related table, it is the best for user_uuid and dept_uuid to establish a single-column index idx_user,idx_dept.

The index idx_user is applicable to the department where the person is located through the personnel ID, and the index idx_dept is suitable to query the personnel under the department through the department.

Thank you for reading! After reading the above, do you have a general understanding of how to create index graphics and text for join tables associated with two tables in MySQL? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.