Serge Semashko <[EMAIL PROTECTED]> wrote: >> > We started with using sqlite3, but the database has grown now to > something like 1GB and has millions of rows. It does not perform as fast > as we would like, so we looked for alternatives. We tried to convert > it to both mysql and postgresql and tried to run the same query we are > using quite often (the query is rather big and contains a lot of > conditions, but it extracts only about a hundred matching rows). The > result was a bit surprising. Mysql just locked down and could not > provide any results. After killing it, increasing memory limits in its > configuration to use all the available memory, it managed to complete > the query but was still slower than sqlite3 (lost about 30%). Postgresql > on the other hand was a really nice surprise and it was several times > faster than sqlite3! Now we are converting to postgresql :) >
PostgreSQL has a much better query optimizer than SQLite. (You can do that when you have a multi-megabyte memory footprint budget versus 250KiB for SQLite.) In your particular case, I would guess you could get SQLite to run as fast or faster than PostgreSQL by hand-optimizing your admittedly complex queries. -- D. Richard Hipp <[EMAIL PROTECTED]>
