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?
_______________________________________________
use-revolution mailing list
[email protected]
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
