Also say SQL Server paging query
It is now generally used to have the following two methods:
1. Select Top @Pagesize * from table1 where id not in (select top @pagesize * (@ Page-1) ID from table1 order by id) Order by ID
2. Select * from (select top @Pagesize * from (select top @ PageSize * @ page * from table1 order by id) a order by id desc) b Order by ID
Which way is better? I tried it.
Do two tables, each 10,000 records, a table ID has Index, one is not
Figure 1, there is no INDEX Table, take the 1000-1100 records
Figure 2, there is no INDEX Table, take the 9000-9100 records
Figure 3, INDEX, take 1000-1100 records
Figure 4, INDEX, take the 9000-9100 record
It can be found that the SORT field has established index conditions, and the first method is very fast, especially when checking the number of back pages. The reason is in the second method, after the first time SELECT, do the self-inquiry When INDEX is already. Sort will very much effect.
