Subject Re: [firebird-support] How to make my report quicker
Author Svein Erling Tysvaer
Alexandre Benson Smith wrote:
> Sean wrote:
>> I would create a compound index on StoreID + SalesDate -- this would
>> narrow the range of your processing.
>> Sean
> Set wrote:
>> Try creating another index (storeid, salesdate)
> I wrote:
>> Create another index on A.SalesDate
> Heors2lover said that PK on table on Table PluWeek is storeid/plu/salesdate
> My question is:
> Won't FB combine the PK index (for the StoredID) and a simple index on Sales Date
> to narrow down the result set as if there is a compound index on (StoreID, SalesDate) ?
> To Heroes3Lover:
> By the way, did you increase your performance with our suggestions ?
> see you !

Yes, Alexander, it should be able to combine these two indexes to
provide better performance than using the primary key alone. I have read
(not tried, I find indexes on single fields simpler to understand and
rarely use compound indexes) that having compound indexes are slightly
faster than single indexes, so to get the last inch of performance out
of a critical query it sounds sensible to try. Though, on the other
side, for totals for all stores, a separate index on SalesDate is
useful, whereas the combined index is useless.

In this particular case, I think having a separate index on SalesDate
would be benefitial in all cases, whereas the combined index Sean and me
suggested will be slightly better for each store, but useless when
querying all stores together. Hence, I think a separate index on
SalesDate sounds like the best thing to try first to fix the major problem.