|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 sourceI don't know, others will tell you. But I can suggest some improvements to
>to explain optimising indices for interbase ?
your "test" case. Use the JOIN syntax, and between rather than >= and <=, i.e.
SELECT sum(routing.subtotal) as SumAgentTotal
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
I assume you have set primary keys for all your tables (probably hub.hub,
waybill.waybill, agent.agent and routing.routing?).