Photios Loupis (4T) wrote:

>We have a table with about 2 million rows (and growing) in it and we
>need to access the data in
>the table for customer support queries. There are 3 main fields that
>are used to identify the data
>for the customer query, namely:
>
>LogDate TIMESTAMP
>Field1 VARCHAR(21)
>Field2 VARCHAR(21)
>
>The SQL statement looks something like this
>
>select "Id","LogDate","Field3" from "TableName"
>where ("LogDate" BETWEEN 'Date1' and 'Date2') AND
>(("Field1"='String1') OR ("Field2"='string1'))
>order by "LogDate"
>
>I am using a composite descending index on the 3 fields,
>ie "LogDate","Field1","Field2" and I am
>not getting the performance that I need. There will never be more
>than 5 records that share the
>same "LogDate" value, but there could be a lot of duplicate data in
>the other 2 fields
>
>Are composite indexes the best way to go in this case?
>
>

I think condition

("LogDate" BETWEEN 'Date1' and 'Date2') AND
(("Field1"='String1') OR ("Field2"='string1'))
will hardly use
"LogDate","Field1","Field2" index. (Because condition OR Field2=... would force to scan whole part of interval cut out with dates)
I would suggest to play with several modifications of indices :
a)
idx1 LogDate,Field1
idx2 LogData,Field2
b)
idx1 LogDate
idx2 Field1
idx3 Field2
Also, if you will not gain enough perfomance consider SP, or check some joins on same table on itself, by making query to use needed indices:
select * from MyTable tb1
inner join MyTable tb2 on tb1 .... tb2
where
tb1.LogDate between ....
and
(
tb2.LogField1 = x
or
tb2.LogField1 = y
)

And take a look of your db tool manager plan analysis part. It usually helps in such cases.
For most dificult parts, with extremely fast perfomance needed we usualy make several indices and later play with query activating/deactivating apropriate of them.