>> Although - and not many people seem to understand this part - indexes
>> make looking up *one* element much faster (if you index the right
>> thing). However, if you're doing to end up needing to process a large
>> percentage of the table anyway, it's actually faster to *not use* any
>> indexes there might be.
> 
> Really? I'll go and discard everything I know about using indexes right
> away....
> 
> If you're processing a portion of a table and you have an index that covers
> the required columns and finds you the portion of the table you want, it's
> much, much better to use the index than to scan the table. Think about it
> this way. If you need all the entries in the phone book where the first
> letter of the surname in between N and T, would you prefer the phone book
> ordered by surname or would you prefer an unorganised heap of phone book
> entries?

My point is that if you need to process 75% of the rows of the table, 
it's probably faster to do a sequential full scan than to use an index 
and do lots of random I/O. Obviously if you only need to process, say, 
5% of the table, you'd be mad to not use an index...

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message