joins are slow ?

[olivier]

valentina 1.90
RB 5.5.3

Hi Ruslan and list,

Join them between two tables are sometimes very slow. It is normal?
I give you an example with a db of 378 000 records without join, and 
another db with the same data but consisted of two connected tables.
The requests on both connected tables are fast if clauses (where...) 
are precise.
But if it is not the case, the requests are very slow. What is not the 
case with the same db but without join.

It is normal? Or it is a defect of Valentina?
Naturally, in a lot of case, we cannot take place of joins and the 
requests are very often precise. But it can be very boring in certain 
cases, I think. Especially that my example was a join with two very 
simple tables, but with one join complexes?

DATABASE 1 (one table without join)

TABLE

- Field "Adress" : varchar, 100, indexed
- Field "Zip" : string,5, indexed
- Field "City" : varchar,60, indexed

378 500 records.

- SELECT adress,zip,city FROM table :
last (0.68 secondes) : OK

- SELECT adress,zip,city FROM table WHERE zip=20100
very last (358 records) : OK

- SELECT adress,zip,city FROM table WHERE zip>10000
last (228 000 records) 1.5 sec : OK

- SELECT adress,zip,city FROM table WHERE zip>20000
very last (17 000 records) : OK

-----------------------------------------------

DATABASE 2 (two tables with join)

TABLE A

- Field "Zip" : string,5,indexed
- Field "City" : varchar,60,indexed

42 500 records.

TABLE B

- Field "Adress" : varchar,100,indexed
- Field "ZipCityPtr",indexed

378 500 records.

- SELECT adress,zip,city FROM tableA, tableB :
VERY SLOW  (20 sec)

- SELECT adress,zip,city FROM tableA, tableB WHERE zip=20100 :
last (358 records : 0.5 sec) : ok

- SELECT adress,zip,city FROM tableA,tableB WHERE zip>10000 :
VERY SLOW (228 000 records : 19 sec)

- SELECT adress,zip,city FROM tableA,tableB WHERE zip>20000 :
VERY SLOW (17 000 records : 15 sec)

- SELECT adress,zip,city FROM tableA,tableB WHERE zip>10000 and 
zip<21000 :
hmm 228 000 records : 3.3 sec

- SELECT adress,zip,city FROM tableA,tableB WHERE zip=10000 and 
left(adress,6)=STREET :
very last : 1 record.

thank you

olivier