Subject Re: [firebird-support] Index Advice - Searches on a Large Table
Author Ivan Prenosil
> 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:
> 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?

If this part of your query is reasonably selective
"LogDate" BETWEEN 'Date1' and 'Date2'
than simple index on LogDate should be enough.

If you think that Field1 and Field2 should also use index,
you have to create separate index for each of them.

But the most problematic part of your query is
order by "LogDate"
For such large table you should never sort result using index. Try
order by "LogDate" + 0