I'd probably move the analyze out of the loop. Since your joining on props.id a better index pind might be create index pind on props (id, pnam)
The name of column id in table props would be clearer as obj_id since it is not the id of the property but the id of the record in the obj table. On 2/2/2011 3:23 PM, 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 > > 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. > > -J > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
