Russ, your slow query

[Russ Tyndall]

On 6/2/06 8:04 AM, "Ruslan Zasukhin" <sunshine at public.kherson.ua> wrote:

> Hi Russ,
> 
> The first thing I have discover - you have join on state fields
> 
> 
> select *
> from locations, statetable
> where locations.state = statetable.state
> 
> 
> But in table locations it is not indexed.
> This cause non-indexed searches.
> 

Somehow, I made a mistake when I sent you the test db; the "real" db has
both of those fields indexed.  But you have perhaps taught me something else
that I need to know:

Do both fields in a join need to be indexed?  What if one of the tables in
the join has only 2-3 records? In other words, is indexing worthwhile in
tables with only a few records?

> This is not main show stopper.
> 

No, although I somehow made a blunder with the test db I sent you, the
"real" dbs have both of those fields indexed and the problem still occurs
(the query with "and state = 'NC' " takes too long to run).

> I see another non-indexed scan of table, which take 70% of time.
> I will check it now and let you know
> 

Great. Identical query works blazing fast in Valentina 1.x version of
project, so I presume some tiny glitch causing a "stall" or such.

Just in case it is helpful to you: Sometimes (not in all cases), I have
regained normal Valentina speed by changing such statements "if state = 'NC'
" to syntax like this: "if state in('NC')".

Russ