On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote: > Andreas Kupries wrote: > > > It seems to me that you are looking for > > http://en.wikipedia.org/wiki/Database_normalization > > > > 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. 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. > 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
