Subject RE: [firebird-support] when an index is needed ?
Author Leyne, Sean
> From with amount of record in a table it's start to be usefull to have an index
> ?
> 100? 1000? 10000 ?
> Actually i thing about 200 ? maybe 1000 ?

Adding an index is not about the number of rows/records, it is about the "uniqueness" of the index.

Consider, A table with every person in the world:

- it should not have an index for sex.
- it also not have an index for Country (China + India = 25% of database)
- A compound index by country, last name, date of birth would be of some value.

You want to build few indexes that help narrow down the scope of the search.

Remember that the engine will need to read each candidate row to check the current search field values against the query critertia.

In some cases, indexes actually slow things down by causing a large number of random disk IOs. A non-indexed SQL is not always a bad thing.