1

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.

DVK
  • 126,886
  • 32
  • 213
  • 327
  • See also: http://stackoverflow.com/questions/1463520/does-having-several-indices-all-starting-with-the-same-columns-negatively-affect/1463604#1463604 – Jonathan Leffler Sep 23 '09 at 01:57

2 Answers2

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • My query is "WHERE date_column = DATE '2009-09-13'". No security ID in where clause, only in select list. – DVK Sep 23 '09 at 02:10
  • The selectivity of other fields is equal to each other, at elast the fields whose ordering I may consider changing. I assume that would make the order in the index irrelevant. – DVK Sep 23 '09 at 02:11
  • If the only relevant criterion is on the date column, it is crucial that the date column is listed first in the index. If there are other criteria on the other fields, it is helpful if the field that is mentioned most often appears earlier in the list of indexed fields; if there is one field that always has a criterion specified, it should come after the date, most likely. – Jonathan Leffler Sep 23 '09 at 03:47
  • Yah, the last one makes sense. – DVK Sep 24 '09 at 12:30
1

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.

PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
  • "The problem is an absence of a test system" I do not quite understand how to test effects of optimizations on a database performance when most tsql operations on tables even with million rows show 0ms of completion time. Anyway, I'd be grateful if you answer it also in http://stackoverflow.com/questions/4118156/what-are-the-first-issues-to-check-while-optimizing-an-existing-database – Gennady Vanin Геннадий Ванин Nov 07 '10 at 20:59
  • 1
    @vgv8: I am pointing out that the quoted stmt is false. The real obstacle is absence of a test system. This is the kind of issue that needs to be fiddled with, changing the order of cols in an index, measured before/after, and tested. It is very dependent on the site/table. trying to have the question answered before testing is very narrow; testing will open up varius possiblities, and various arrangements of the col order should be tested, not just one. – PerformanceDBA Nov 10 '10 at 10:00