gistfile1.txt

offset bad?

https://developer.wordpress.com/2014/02/14/an-efficient-alternative-to-paging-with-sql-offsets/

SELECT * FROM my_table OFFSET 8000000 LIMIT 100;
But on a performance level, this means you’re asking your DB engine to figure out where to start from 
all on its own, every time. Which then means it must be aware of every record before the queried offset, 
because they could be different between queries (deletes, etc). So the higher your offset number, the longer 
the overall query will take.

real test;

SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 0 ms:687
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 2500 ms:779
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 5000 ms:779
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 7500 ms:840
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 10000 ms:917
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 12500 ms:871
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 15000 ms:915
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 17500 ms:992
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 20000 ms:986
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 22500 ms:981
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 25000 ms:964
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 27500 ms:1040
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 30000 ms:1027
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 32500 ms:1034
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 35000 ms:1062
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 37500 ms:1052
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 40000 ms:1054
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 42500 ms:1115
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 45000 ms:1110
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 47500 ms:1061
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 50000 ms:1092
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 52500 ms:1208
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 55000 ms:1197
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 57500 ms:1212
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 60000 ms:1251
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 62500 ms:1242
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 65000 ms:1241
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 67500 ms:1242
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 70000 ms:1275
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 72500 ms:1352
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 75000 ms:1342
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 77500 ms:1326
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 80000 ms:1486
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 82500 ms:1352
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 85000 ms:1361
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 87500 ms:1388
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 90000 ms:1394
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 92500 ms:1424
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 95000 ms:1370
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 97500 ms:1391
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 100000 ms:1411
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 102500 ms:1434
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 105000 ms:1407
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 107500 ms:1443
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 110000 ms:1449
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 112500 ms:1430
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 115000 ms:1447
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 117500 ms:1477
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 120000 ms:1420
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 122500 ms:1382
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 125000 ms:1500
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 127500 ms:1516
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 130000 ms:1568
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 132500 ms:1624
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 135000 ms:1614
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 137500 ms:1601
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 140000 ms:1593
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 142500 ms:1655
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 145000 ms:1642
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 147500 ms:1716
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 150000 ms:1588
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 152500 ms:1606
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 155000 ms:1603
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 157500 ms:1745
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 160000 ms:1742
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 162500 ms:1760
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 165000 ms:1689
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 167500 ms:1671
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 170000 ms:1696
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 172500 ms:1717
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 175000 ms:1686
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 177500 ms:1754
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 180000 ms:1823
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 182500 ms:1723
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 185000 ms:1849
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 187500 ms:1780
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 190000 ms:1776
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 192500 ms:1883
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 195000 ms:1911
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 197500 ms:1934
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 200000 ms:1961
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 202500 ms:1848
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 205000 ms:1862
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 207500 ms:1956
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 210000 ms:1974
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 212500 ms:1986
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 215000 ms:2029
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 217500 ms:2007
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 220000 ms:2033
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 222500 ms:1998
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 225000 ms:2035
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 227500 ms:2098
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 230000 ms:2045
SELECT * FROM event_stream event_stream ORDER BY created_at ASC, version ASC LIMIT 2500 OFFSET 232500 ms:1966