----- Original Message -----
From: "Svein Erling Tysvær" <svein.erling.tysvaer@...>
To: <ibobjects@yahoogroups.com>
Sent: Friday, July 27, 2001 10:13 AM
Subject: Re: [IBO] Slow select...


> >I created a index on cep (cep_cidade, cep_endereco). Now the result is
> faster.
> >Is it right to use this way? Can I find problems in the future creating
> indexes?
>
> Sounds like a fair index to have, although the query you originally showed
> us will not be able to benefit from the second half of the index. If there
> are lots of records having the same combination of these two fields, you
> may want to include the primary key as the third field in the index as
well
> (many duplicates slow down updates). The only problem you're likely to
> discover, is that IB occationally choose a bad plan and that you'll have
to
> change your SQL a bit to reduce the number of indexes being used. Say,
>
> SELECT *
> FROM table
> WHERE A STARTING WITH 'J'
> AND B STARTING WITH 'JA'
>
> could be slow if both A and B had an index. In such a case it can be wise
> to change so that no index is used for the least restricting criteria,
e.g.
>
> SELECT *
> FROM table
> WHERE (A STARTING WITH 'J' OR 3=2)
> AND B STARTING WITH 'JA'
>
> 3=2 is of course never true, but because of the OR, Interbase cannot use
an
> index for A and the query may be able to execute faster. But all this is
> just minor thinkering with your SQL which you will discover as you learn.
> Get hold of the IB_SQL utility (freeware) from www.ibobjects.com and try
> your statements for yourself. You can learn a lot from preparing the SQL
> and watching the generated plan.
>
> Set
>
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>