Create an r-tree index on poitable(poiid), the query time of your SQL will drop to 1ms. http://www.sqlite.org/rtree.html ________________________________ ???: ???<mailto:2004wqg2008 at 163.com> ????: ?2016/?2/?17 16:34 ???: sqlite-users at mailinglists.sqlite.org<mailto:sqlite-users at mailinglists.sqlite.org> ??: [sqlite] A question
I have a question: there are two tables: CREATE TABLE poiTable ( poiId INTEGER NOT NULL, catId INTEGER NOT NULL, mortonCode INTEGER NOT NULL, versionId INTEGER NOT NULL, iconSetId INTEGER , catIconSetId INTEGER , brandIconSetId INTEGER , regionId INTEGER , attrBitMask INTEGER , attrDisplayBitMask INTEGER , attributeBlob BLOB , primary key (poiId, catId) ); table index: CREATE INDEX idx_poiTable_0 ON poiTable(catId); CREATE TABLE poiVirtualTileTable (tileId INTEGER NOT NULL, minId INTEGER NOT NULL, maxId INTEGER NOT NULL, versionId INTEGER NOT NULL, isDirty INTEGER NOT NULL, primary key (tileId, minId) ); The total number of records of poiTable is 383826; The total number of records of poiVirtualTileTable is 9791; sql1:SELECT a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob FROM poiTable a WHERE a.catId IN(8449,8450,8452) Execute sql1 consumes 500-600ms, returned 20232 records sql2:SELECT a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob FROM poiTable a,poiVirtualTileTable b WHERE a.poiId >= b.minId AND a.poiId <= b.maxId AND b.tileId = 557467343 Execute sql2 , returned 157 records sql3:SELECT a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob FROM poiTable a,poiVirtualTileTable b WHERE a.poiId >= b.minId AND a.poiId <= b.maxId AND b.tileId = 67430686 Execute sql3 , returned 92231 records sql4:SELECT a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob FROM nds111004.poiTable a,nds111004.poiVirtualTileTable b WHERE a.poiId >= b.minId AND a.poiId <= b.maxId AND b.tileId=557467343 AND a.catId IN(8449,8450,8452) Execute sql4 consumes 500-600ms, returned 5847 records sql5:SELECT a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob FROM nds111004.poiTable a,nds111004.poiVirtualTileTable b WHERE a.poiId >= b.minId AND a.poiId <= b.maxId AND b.tileId=67430683 AND a.catId IN(8449,8450,8452) Execute sql5 consumes 500-600ms, returned 14 records question: the records returned of sql5 is less than sql4, but the time is almost the same ? _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
