Sybase 12.4.2 manual Selecting an index, 150, Criteria to identify Index to select

Adaptive Server IQ index types

AND l_suppkey	=	s_suppkey
AND c_nationkey	=	s_nationkey
AND s_nationkey	=	n_nationkey
AND n_regionkey	=	r_regionkey
AND r_name	=	’ASIA’
AND o_orderdate	>=	’1994-01-01’
AND o_orderdate	<	’1995-01-01’
GROUP BY n_name
HAVING n_name LIKE "I%"

AND SUM(l_extendedprice*(1-l_discount)) > 0.50

ORDER BY 2 DESC

All columns referenced in this query except l_extendedprice and l_discount should have an LF or HG index.

Selecting an index

Here is a quick chart that summarizes how to select an index type.

Criteria to identify	Index to select
Note indexes created automatically on all columns.	Default index
Note indexes created automatically on columns with	HG with UNIQUE
UNIQUE or PRIMARY KEY constraint.	enforced
Identify all columns used in a join predicate and choose	HG
the index type depending on the number of unique values.	or
	LF
Identify columns that contain a low number of unique	LF
values and do not already use multiple indexes.
Identify columns that have a high number of unique	HG
values and that are part of a GROUP BY clause in a select
list in a SELECT DISTINCT or DISTINCT COUNT.
Identify columns that may be used in the WHERE clause	HG
of ad hoc join queries that do not already have HG or LF	or
indexes.
	LF
Identify columns that have a high number of unique	HNG
values and that will not be used with GROUP BY,
SELECT DISTINCT or DISTINCT COUNT.
Look at any remaining columns and decide on additional
indexes based on the number of unique values, type of
query, and disk space. Also, for all columns, be sure that
the index types you select allow the data type for that
column.

150