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.

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.

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

1. For a query on a table, only one index is used at most each time

2. 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

3. 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

4. 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).

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.

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.