Index question

[andreis]

Hi:
Is there a rule of thumb as to how many records average-size table should have so that index is needed over scan?
All density-related issues aside, we have tables with 50, 100, 300, 700 and over 1000 records, and we are not sure that small searchable tables need indexes at all.
Thanks.
Andrei

 

[dbmsguy]

the optimizer being a cost based animal will always choose the cheapest way to get information.

the rot I was familiar with was a sybase ase rot and that was 10 pages. anything less than that would always get you a table scan. that rdbms of course has a 2k page size, not 8 like sql server 7.0.

consider this though.....

1. an index is no expense to a reference table which this sounds like. so if that is the case, put the index on anyways.
2. indexes are not only for speed of access but data integrity (like the primary key). so you might want the index on for the primary key.

hope this helps Paul

http://www.dbmsguy.com

 

[andreis]

Paul, thanks for your advice. We decided to leave tiny tables with no indexes because they are being scanned anyway. Creating primary key for integrity, though, sounds like a good idea...
Andrei