"Database" optimized sorting && efficient paging

Example:

Select <cols> from profiles where sex = ' M ' ORDER by rating limit 10;

The order By,limit is used at the same time and is slow if no index is available. And the sey choice is very low, you can add some special index to do the sorting. For example, create an (sex,rating) index.

Even if there is an index, the query can be very slow if the user needs to turn the page and page to the back of the comparison.

The following query is then paged through the combination of the order by and limit offsets to the very back:

Mysql>select <cols> from profiles where sex = ' M ' ORDER by rating limit 1000000,10;

Regardless of whether you create an index, this query is a serious problem. Because as offsets increase, MySQL takes a lot of time to scan for data that needs to be discarded.

Deserialization, pre-evaluation, and caching may be the only policies that solve such problems. A better approach is to limit the number of pages a user has, and it will not actually have much impact on the user experience, as users are rarely concerned with the 10,000th page of search results.

Another good strategy for optimizing such indexes is to use deferred correlation to return the required primary key by using the Overwrite index query, and then to get the required rows based on the primary key associated with the original table. This can reduce the number of rows that MySQL scans for those dropped. The following query shows how to efficiently use (sex,rating) indexes for sorting and paging.

Mysql>select <cols> from profiles inner JOIN (

Select <primary key cols >from Profiles

->where x.sex = ' M ' ORDER by rating limit 100000,10

->as x using (<primary key cols>);

Reference: "High performance MySQL"

"Database" optimized sorting && efficient paging