On Wed, Jun 4, 2008 at 7:12 AM, Christophe Leske <[EMAIL PROTECTED]> wrote:
> Wilson, Ron P schrieb: > > I'm not a guru yet, but I think you are not using the latlon index in > > your query. Perhaps if you index on lat and lon separately your query > > will use those indices. I think the lines below indicate using the > > indices on class_dds and rowid. > > > > Thanks also for the tiling hint, but my application is already written, > and I have to stick to the databse given. I might reconsider though if > the performance is still bad. > Does that mean you can't change the application at all? Remember, the tiling (I call it "blockmap" since that's what Doom called it) method doesn't require changing your existing table layout; it only requires adding new tables. Furthermore, the contents of these new tables can be computed from the existing data in the Cities table -- all your application would need to do is check for the existence of those tables, and if it doesn't find them, create them and populate them. It'd be an expensive initial setup, but you only have to pay it once. Also recall that SQLite supports multiple databases: attach 'blockmap.db' as bm; -- the following two tables are created in the blockmap.db file because they have the 'bm.' qualifier create table bm.blockmap (id integer primary key, lat real, long real); create table bm.blockmapCity (blockmapId integer not null, cityId integer not null); -- but you don't need to use the 'bm.' qualifier, *unless* more than one of the attached databases has a blockmapcity -- by the way: tmp_blockfilter is a temporary table containing blockmap IDs. SQLite won't use an index for an IN clause, but it will use one if you're joining against another table. select c.* from Cities c join blockmapCity bc on bc.cityId=c.id join tmp_blockfilter tb on tb.blockmapId = bc.blockmapId; -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
