Nicolas Williams wrote: > On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote: > >> SQLite seems to do quite poorly performance-wise with fully-normalized >> attribute tables like this, when you want to query against multiple >> attributes. My timing comparisons with postgres show sqlite to be as >> much as 10x-15x slower than pg. >> >> My timing code is at http://paste.tclers.tk/2346 > > You need an index on props(id) -- you always need an index on columns > that form a foreign key. SQLite3 requires that if you want to cascade > deletions.
Ok, that caught me by surprise, but it improved things a lot. With that index sqlite is now significantly faster than pg on 3 of the tests, but still similar to or slower than pg on the remaining 2 - in my code, "sx" and "sx3". (I can't be sure of the exact timings because I can't run sqlite and the pg server on the same machine) Any idea why pg does ok on these queries without the extra index - Maybe they're created by default? SQLIte doesn't create any indexes automatically on primary key fields or anything else, correct? > Without that index your joins will use full table scans. > > Also, it's better to use FROM table1 t1 JOIN table2 t2 ON t1.col1 = > t2.col2 than to put the join conditions in the WHERE clause, as that's > much easier to read. But I can see that you have JOINs using that > un-indexed column. I wrote this test code some time ago, I think originally against a database that didn't support JOIN syntax. I would use that were I rewriting it now. (I also don't remember exactly what conditions I was trying to exercise, but I think it was the case where two attributes each match a large set but the intersection of those sets is small) Thanks, -J > >> This is a synthetic test, but I ran across the issue in a real >> application. I'm not sure what else I can do do optimize the queries; >> using a denormalized table is the only thing that seems to help. > > Add the missing index. > > Nico _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
