Asking for advice on a mysql table grouping index problem

Question

I'm working on a website program, and the general requirements are as follows. Users are divided into five levels: 1-5. The higher the number, the higher the authority. I have a bunch of content, the higher the level the more content is visible to the user. For example, there is content: A, B, C, D, E. Visible to user group 1: Visible to user group 2 of A: Visible to user group 5 of A, B...

学习ing · Answer

Actually, your idea is already right.

Create an index on tid and divide the tables according to group.

If group >= 3 groups, dynamically combine sql in the program as follows:

select * from group3 where tid < 100
union all 
select * from group4 where tid < 100
union all 
select * from group5 where tid < 100

The above index is effective and the logic is available.

ringa_lee · Answer

First of all, let me explain that in Innodb, whether the index takes effect or not has nothing to do with your use of < or >. It does not mean that using = will definitely allow you to use indexes. When the performance of full table query is higher than that of index retrieval query, MySQL will intelligently abandon the index and choose full table query.

As shown in the picture:

Back to your question, if the range retrieved by an index, such as tid<100, is relatively small, the index can be used.

If the result sets of these two indexes are large, should you consider adding other filtering conditions, such as only searching for content in the past month based on the creation time.

Pagination issues can also be filtered again by primary key ID.

仅有的幸福 · Answer

First of all, you need to understand the following points:

For a query on a table, only one index is used at most each time
For the joint index, the data is filtered from left to right, so if the first filter condition targets greater than or less than, the second filter condition will not have an exact index range in the entire optional area. Run through all the data filtered out by the first filter
The structure of the B-Tree index is similar to a tree structure, as shown in the figure below. The joint index is retrieved from left to right. The starting point is the process of searching branches from top to bottom in this structure
The index mechanism is simply to create a corresponding table from values to data items, so that you can quickly locate a certain value in a certain field to a certain row, eliminating the need to run the entire table to find the corresponding row, so compare Quick

Structure of B-Tree index:

Then back to your question, if you want to greatly improve efficiency, then the first step of joint indexing needs to significantly reduce the amount of data that can be used for subsequent screening, so if you want to check tid , Filtering with tid first can significantly reduce subsequent B-Tree index branches, so if you want to use a joint index, it should be (tid, group).

怪我咯 · Answer

The filtering performance of group conditions is very poor, and it makes little sense to create an index alone.

According to the scenario you describe, as long as the value of tid is not too large (on the order of thousands), it is enough to create an index for tid.
If you are still worried about the large amount of data filtered by tid conditions, you can create a combined index of tid and group.

黄舟 · Answer

First of all, thank you very much for your attention and answers to my questions! !
After solving the problem, I have some thoughts on boxsnake’s suggestions, and I’ll post them here.
group_tidIn addition to solving the problem of reading, this indexing method can also solve the paging problem.
For example, if the number of articles per page is 10 and the user level is 3, then when reading, it will be from group1, group2, and group3 respectively.
Press Scope tid<100, take 10 articles each. Even if there are no qualified results in a certain group, the sum of several items can cover them all.

But if I use the index method tid_group to read, if groupFor example, if you take 10 articles, tid90-tid99, if their groups are all 4, then you cannot get the values that meet the conditions.
And tid_group must limit tid before limiting group, so it cannot be used.