Does the order of columns on a covered index in Sy

问题:

We have a large table, with several indices (say, I1-I5).

The usage pattern is as follows:

Application A: all select queries 100% use indices I1-I4 (assume that they are designed well enough that they will never use I5).

Application B: has only one select query (fairly frequently run), which contains 6 fields and for which a fifth index I5 was created as a covered index.

The first 2 fields of the covered index are date, and a security ID. The table contains rows for ~100 dates (in date order, enforced by a clustered index I1), and tens of thousands of security identifiers.

Question: dies the order of columns in the covered index affect the performance of the select query in Application B?

I.e., would the query performance change if we switched around the first two fields of the index (date and security ID)? Would the query performance change if we switch around one of the last fields?

I am assuming that the logical IOs would remain un-affected by any order of fields in the covered index (though I'm not 100% sure).

But will there be other performance effects? (Optimizer speed, caching, etc...)

The question is version-generic, but if it matters, we use Sybase 12.

Unfortunately, the table is so huge that actually changing the index in practice and quantitatively confirming the effects of the change is extremely difficult.

回答1:

It depends. If you have a WHERE clause such as the following, you will get better performance out an index on (security_ID, date_column) than the converse:

WHERE date_column BETWEEN DATE '2009-01-01' AND DATE '2009-08-31'
  AND security_ID = 373239

If you have a WHERE clause such as the following, you will get better performance out of an index on (date_column, security_ID) than the converse:

WHERE date_column = DATE '2009-09-01'
  AND security_ID > 499231

If you have a WHERE clause such as the following, it really won't matter very much which column appears first:

WHERE date_column = DATE '2009-09-13'
  AND security_ID = 211930

We'd need to know about the selectivity and conditions on the other columns in the index to know if there are other ways of organizing your index to gain more performance.

Just like your question is version generic, my answer is DBMS-generic.

回答2:

Unfortunately, the table is so huge that actually changing the index in practice and quantitatively confirming the effects of the change is extremely difficult.

The problem is not the size of the table. Millions of rows is nothing for Sybase.

The problem is an absence of a test system.