Posted by Robert Klemme on 10/26/05 13:21

gilles27@talk21.com wrote:
> I'm sure Gert-Jan is correct in what he states, however because the
> most likely filtering on OrderLines is by Ledger and OrderNumber,
> those two should come before OrderLineNumber in the primary key.
>
> I could ask the customer to demonstrate their findings but my pride
> prevents me from admitting to them that I don't know what they mean by
> "reduced index reads by a factor of 5". :-)
>
> I need some of way checking that my changes to primary keys are
> actually having a positive effect, if I could compare the number of
> index reads before and after modifying the table, that would help.

I'm afraid, that is not so easy. It starts with the objectives: what do
you (did they) want to optimize? Is it execution time of a single query,
is it execution time of a set of queries? You need to know the usage
patterns. Then there's data: does it change often / seldome? How is it
distributed? Measures: you can not only change order of columns in this
index but you can also add another index etc...

When I try to optimize slow queries I usually start with a look at the
execution plan because that is easily obtained. Often you can see that an
index is missing / not used etc. immediately.

Kind regards

robert

[Back to original message]