performance - MySQL Select with Offset is Faster Than no Offset -

i've been messing around query performance system pagination make data selection fast possible, i've come across don't quite understand. knowledge, when limit offset used, mysql has iterate through each row before offset , discard them, in theory query offset of 10,000 slower 1 without, true in case

select sql_no_cache * `customers` `networkid`='\func uuid()'      order `datetimeadded` desc limit 0, 100; /* finishes in 2.497 seconds */   select sql_no_cache * `customers` `networkid`='\func uuid()'     order `datetimeadded` desc limit 10000, 100;  /* finishes in 2.702 seconds */ 

but, if use inner join join table userid column doing sorting , limiting, it's consistently faster offset of 10,000 without one, stumps me. example here be

select sql_no_cache * `customers`      inner join (select `userid` `customers` `networkid`='\func uuid()'          order `datetimeadded` desc limit 100)      `results` using(`userid`) /* finishes in 1.133 seconds */  select sql_no_cache * `customers`      inner join (select `userid` `customers` `networkid`='\func uuid()'          order `datetimeadded` desc limit 10000, 100)      `results` using(`userid`) /* finishes in 1.120 seconds */ 

why query using offset faster query without offset?

---

explains:

i have posted google docs spreadsheet here explains content here

_{note: tests above done in php looping 20 times each}

^{note2: customers view, not base table}

case 1: optimizer can use index on order by. limit 10 faster limit 10000,10 because can stop reading rows sooner.

case 2: optimizer cannot (or chooses not to) use index order by. in case, entire set of rows (after where) collected, set sorted, , offset , limit applied. in case value of offset makes little difference; of time consumed fetching rows, filtering them, , sorting them.

index(x,y) select ... x=2               order y limit ... -- case 1 select ... x=2 , deleted=0 order y limit ... -- case 2  index(networkid, datetimeadded)         -- composite select ... networkid='...' order datetimeadded desc ... -- case 1  index(networkid), index(datetimeadded)  -- separate select ... networkid='...' order datetimeadded desc ... -- case 3 

case 3 might case 1 because might use index(datetimeadded). or, of optimizer chooses use other index, slow case 2. anyway, not using composite index can handle both where , order by.

if can manage case 1, recommend "remember left off" make pagination more efficient. see my pagination blog.

more on creating indexes.