Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


10 - Understand Index Column Placement

The placement of columns in a multi-column index is critically important in determining whether the index is used to optimize queries or not. Figure 10A shows that a two-column index is used for queries involving the first column but it isn't used at all if the query only specifies the second column.

Figure 10A - Queries Involving A Multi-Column Index 
CREATE TABLE query1

   ( primary1 INTEGER NOT NULL,

     search1 INTEGER NOT NULL,

     search2 INTEGER NOT NULL,

     data1 INTEGER NOT NULL,

     PRIMARY KEY ( primary1) );


CREATE INDEX iquery1 ON query1

   ( search1, search2 );


SELECT * FROM query1 WHERE search1 = 44;

   Estimated 1 rows in query (I/O estimate 2)

   PLAN> query1 (iquery1)


SELECT * FROM query1 WHERE search1 = 44 AND search2 = 44;

   Estimated 1 rows in query (I/O estimate 2)

   PLAN> query1 (iquery1)


SELECT * FROM query1 WHERE search2 = 44;

   Estimated 3 rows in query (I/O estimate 220)

   PLAN> query1 (seq)

This is analogous to using a phone book to search on last name (fast) rather than first name (slow). If it's really important for both kinds of queries to run quickly then it might help to define another index as shown in Figure 10B.

Figure 10B - Defining A Useful Index 
CREATE INDEX iquery2 ON query1

   ( search2 );


SELECT * FROM query1 WHERE search2 = 44;

   Estimated 1 rows in query (I/O estimate 2)

   PLAN> query1 (iquery2)

An index will help optimize a multi-column ORDER BY clause if the placement of columns in the index and ORDER BY clause are exactly the same. In other words, if the ORDER BY says to sort on column 1 before column 2 then an index that sorts on column 2 before column 1 will not be helpful.

[Home] [Table of Contents] [Previous Section] [Next Section]