Massive joins

[viktoras didziulis]

The query needed to left join 3 tables (with 10 000 (species), 30 000 
(donor regions) and 200 (region definitions) records.) on the forth with 
40 000 records (species introduction events) and filter the result list 
of regions using constraints (WHERE...) on 1 or 20 fields. Using LEFT 
JOINs in this case was very slow - up to 10 minutes. I rewrote joins (as 
described in "SQL" by Martin Gruber) in a form like this:

SELECT DISTINCT sir.country, sir.start_year, sir.end_year
FROM species_in_region sir WHERE sir.idspecies IN(
SELECT sp.idspecies FROM species sp WHERE sp.ordo IN(1,10,23,15)
)
AND sir.id_sp_region IN(
SELECT da.id_sp_region FROM donor_areas da WHERE da.regionid 
IN(2,3,4,115,23,21)
)
AND /etc..../
ORDER BY sir.end_year

Implemented in this form it takes less than a second. SQLite is a very 
small database engine and likely (???) does not perform any query 
optimizations internally. In comparison with MySQL, which does optimise 
joins, the same unoptimised query with joins in MySQL database server is 
executed just a little (~0.2 sec.) slower than manually-optimised query 
in SQLite.

Viktoras

Ruslan Zasukhin wrote:
> Can you explain this?
>
>     JOIN on RAM db/tables SqlLite do so slowly ???
>     why? What reason?
>
>