Sybase 12.4.2 manual Default column index, LowFast LF index type, Projections on few rows, 145

CHAPTER 4 Adaptive Server IQ Indexes

Default column index

For any column that has no index defined, or whenever it is the most effective, query results are produced using the default index. This structure is fastest for projections, but generally is slower than any of the three column index types you define for anything other than a projection. Performance is still faster than most RDBMSs since one column of data is fetched, while other RDBMSs need to fetch all columns which results in more disk I/O operations.

Projections on few rows

If a column is used only in projections, even if some of the queries return a small number of rows, Low_Fast and High_Non_Group indexes are redundant because the default structure is equally as fast for projecting a small number of rows.

The Low_Fast (LF) index type

This index is ideal for columns that have a very low number of unique values (under 1,000) such as sex, Yes/No, True/False, number of dependents, wage class, and so on. LF is the fastest index in Adaptive Server IQ.

When you test for equality, just one lookup quickly gives the result set. To test for inequality, you may need to examine a few more lookups. Calculations such as SUM, AVG, and COUNT are also very fast with this index.

As the number of unique values in a column increases, performance starts to degrade and memory and disk requirements start to increase for insertions and some queries. When doing equality tests, though, it is still the fastest index, even for columns with many unique values.

Recommended use

Use an LF index when:

•A column has fewer than 1,000 unique values.

•A column has fewer than 1,000 unique values and is used in a join predicate.

Never use an LF index for a column with 10,000 or more unique values.

145