MySQL: How to optimize pagination of a SQL script which has "order by" on a muliti-column index?

user_4162708 0
Viewed 56

we know that

select * from table order by id limit 1000000,10

can be optimized in this way

select * from table where id > (select id from table  order by id limit 1000000,1) limit 10

but if we are using a multi-column index like idx_ab (a,b), how to optimize the following sql command?

select * from table order by a,b limit 1000000,10
mysql sql
1 Answers
select * from table order by a,b limit 1000000,10

will be somewhat optimized with the help of this composite index: INDEX(a,b).

However, it must step over 1000000 rows, one at a time, before finding the 10 that you want. This is very inefficient, no matter how many columns in the index, etc.

Some "pagination" applications can be sped up by "remembering where you left off", as discussed here: http://mysql.rjweb.org/doc.php/pagination It also has a link to how to do such when the key is composite.

user_17668310
Related