Sybase nonclustered index selection

Question

We have a table with two nonclustered indexs. The two indexes both have the same three columns, in the same order they only differ in that one is sorted ascending the other descending. A developer had created a stored procedure that does a select where he intended to (but forgot!) to force the use of an index rather than do an Order by. When one user runs the query one index is consistently selected (ironically the correct one which masked this error for some time), when another user runs the procedure the other index is returned. What would be different between two users running the exact same procedure that would influence index selection?

(Note: this code will be rewritten, but I am trying to come to an understnading of what went on here for an After Action Report).

Thanks in Advance

Answer 1

Indexes are little more complex than they seem. A database system decides to use index (or not) based on the query plan, table volume, number of rows, database cache. The Database system does a cost estimation (cardinality probability, i/o estimates etc) based on query and above data.

If you have two similar indexes with different sort schemes, there is a chance that the required index key (i) is located at almost n/2 WHERE n=index size

There is also a possibility that based on the data (duplicate data / serial data) in the table, sybase doesn't care about indexes and thus can't decide which one to use.

Drop one index at a time and see what happens.