Comments by "Winnetou17" (@Winnetou17) on "ByteByteGo"
channel.
-
13
-
9
-
4
-
3
-
2
-
2
-
2
-
2
-
2
-
1
-
1
-
1
-
1
-
1
-
@LifeIsCrazyAsShit I had this exact problem in a big table in MySQL several years ago. For example, doing
SELECT whatever FROM table_name WHERE id > 433453 LIMIT 100 OFFSET 1000000;
This killed the performance, because the id column was a clustered index (PRIMARY KEY). It knew to jump to that id immediately, but couldn't compute the offset and also do that jump directly, it had to go through all that one million rows/ids. So the higher the offset, the slower it ran (and that table had between 25 and 35 million rows). The job was to export basically the whole table, but in order, from where it left off. And we had small batches of exports. Once I changed the job to remember the last id, I changed the query to simply be
SELECT whatever FROM table_name WHERE id > 124342343 LIMIT 100;
And voila! the speed was back. No matter the id, if I was at the beggining, middle or end of the table, the query was fast, in constant time basically.
So the lesson is - watch out when using OFFSET. Some indexes allow "jumping", but some don't, and in these cases, high offset values are a performance killer.
1