Subject Re: [ib-support] Re: Query with Paradox much faster than Interbase
Author Svein Erling Tysvær
>I realise Interbase indices work differently - where is a good source
>to explain optimising indices for interbase ?

I don't know, others will tell you. But I can suggest some improvements to
your "test" case. Use the JOIN syntax, and between rather than >= and <=, i.e.

SELECT sum(routing.subtotal) as SumAgentTotal
FROM hub
JOIN waybill ON waybill.orighub = hub.hub
JOIN routing ON waybill.waybill = routing.waybill
JOIN agent ON routing.agent = agent.agent
WHERE costcntr = 2
and agent.ownresource = FALSE
and invdate BETWEEN '08/01/2001' AND '08/31/2001'

As for indexes (one year or so ago, someone explained pretty detailed why
databases used indexes and not indices - I've forgotten the explanation,
but there is a slight difference between the two), it is advantageous to
avoid many duplicates. Hence, if there's plenty of duplicates for each
costcntr, never make an index on that column alone. However, adding an
index on (costcntr, <theprimarykeyfield>) could improve performance for
some queries.

I assume you have set primary keys for all your tables (probably hub.hub,
waybill.waybill, agent.agent and routing.routing?).