Due to too many user records (3 million), paging queries become slow. How to optimize statements or indexes? Do you have any optimization plans?
Thank you for your plan. I was inspired by it, but it is rather special and not suitable for my situation. The details are as follows:
1 when that us opens the participation record page, the record of the latest 10 participants will be displayed. (The records of these 10 people are not the latest ten data in the user participation record table, but the ten data filtered by adding WHERE condition. )
The latest data of 10 participants are obtained from two tables (user information table and user participation record table).
3 to ensure that users can turn over the next page of data, the previous page of data.
Now look up the data is to join the two tables, WHERE the conditions of the two tables, and then limit the data. This is too slow, so I would like to ask what is the optimization plan?
1. Do not scan from 0 to 10002 with limit, limit 1000,20 and 20 without conditions.
2. remember the last user primary key on the previous page and use select * from userwhere uid > lastuidlimit pagesize for paging.
3. The data consistency requirement is not high, so consider adding a layer of cache in the middle of the database.