Vander Clock Stephane wrote: > Hello, > > But i not understand how the R* tree based table can help me here ?
The BETWEEN queries are specifically optimized by R*Tree. > > can you explain me ? > > thanks you by advance > stephane > > On 12/9/2010 8:24 PM, Gabríel A. Pétursson wrote: >> It seems to me that you may want to consider defining your table as a >> virtual R* tree based table. >> >> See http://www.sqlite.org/rtree.html >> >> On Wed, 2010-12-08 at 19:56 +0300, Vander Clock Stephane wrote: >>> Hello, >>> >>> on the table : >>> >>> CREATE TABLE HASH( >>> ID INTEGER PRIMARY KEY ASC, >>> x1_y1 INTEGER, >>> x1_y2 INTEGER, >>> x1_y3 INTEGER, >>> x1_y4 INTEGER, >>> x1_y5 INTEGER, >>> x2_y1 INTEGER, >>> x2_y2 INTEGER, >>> x2_y3 INTEGER, >>> x2_y4 INTEGER, >>> x2_y5 INTEGER, >>> x3_y1 INTEGER, >>> x3_y2 INTEGER, >>> x3_y3 INTEGER, >>> x3_y4 INTEGER, >>> x3_y5 INTEGER, >>> x4_y1 INTEGER, >>> x4_y2 INTEGER, >>> x4_y3 INTEGER, >>> x4_y4 INTEGER, >>> x4_y5 INTEGER, >>> x5_y1 INTEGER, >>> x5_y2 INTEGER, >>> x5_y3 INTEGER, >>> x5_y4 INTEGER, >>> x5_y5 INTEGER >>> ); >>> CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1); >>> CREATE INDEX HASH_X2_Y2_IDX ON HASH (X2_Y2); >>> CREATE INDEX HASH_X3_Y3_IDX ON HASH (X3_Y3); >>> CREATE INDEX HASH_X4_Y4_IDX ON HASH (X4_Y4); >>> CREATE INDEX HASH_X5_Y5_IDX ON HASH (X5_Y5); >>> >>> >>> with millions of rows, how to optimize such query : >>> >>> Select >>> ID >>> from >>> HASH >>> where >>> x1_y1>=<#randomnumber1> and >>> x1_y1<=<#randomnumber1>+ 20 and >>> x1_y2>=<#randomnumber4> and >>> x1_y2<=<#randomnumber4> + 20 and >>> x1_y3>=<#randomnumber7> and >>> x1_y3<=<#randomnumber7> + 20 and >>> x1_y4>=<#randomnumber10> and >>> x1_y4<=<#randomnumber10> + 20 and >>> x1_y5>=<#randomnumber13> and >>> x1_y5<=<#randomnumber13> + 20 and >>> x2_y1>=<#randomnumber16> and >>> x2_y1<=<#randomnumber16> + 20 and >>> x2_y2>=<#randomnumber19> and >>> x2_y2<=<#randomnumber19> + 20 and >>> x2_y3>=<#randomnumber22> and >>> x2_y3<=<#randomnumber22> + 20 and >>> x2_y4>=<#randomnumber25> and >>> x2_y4<=<#randomnumber25> + 20 and >>> x2_y5>=<#randomnumber28> and >>> x2_y5<=<#randomnumber28> + 20 and >>> x3_y1>=<#randomnumber31> and >>> x3_y1<=<#randomnumber31> + 20 and >>> x3_y2>=<#randomnumber34> and >>> x3_y2<=<#randomnumber34> + 20 and >>> x3_y3>=<#randomnumber37> and >>> x3_y3<=<#randomnumber37> + 20 and >>> x3_y4>=<#randomnumber40> and >>> x3_y4<=<#randomnumber40> + 20 and >>> x3_y5>=<#randomnumber43> and >>> x3_y5<=<#randomnumber43> + 20 and >>> x4_y1>=<#randomnumber46> and >>> x4_y1<=<#randomnumber46> + 20 and >>> x4_y2>=<#randomnumber49> and >>> x4_y2<=<#randomnumber49> + 20 and >>> x4_y3>=<#randomnumber52> and >>> x4_y3<=<#randomnumber52> + 20 and >>> x4_y4>=<#randomnumber55> and >>> x4_y4<=<#randomnumber55> + 20 and >>> x4_y5>=<#randomnumber58> and >>> x4_y5<=<#randomnumber58> + 20 and >>> x5_y1>=<#randomnumber61> and >>> x5_y1<=<#randomnumber61> + 20 and >>> x5_y2>=<#randomnumber64> and >>> x5_y2<=<#randomnumber64> + 20 and >>> x5_y3>=<#randomnumber67> and >>> x5_y3<=<#randomnumber67> + 20 and >>> x5_y4>=<#randomnumber70> and >>> x5_y4<=<#randomnumber70> + 20 and >>> x5_y5>=<#randomnumber73> and >>> x5_y5<=<#randomnumber73> + 20; >>> >>> because they takes very very lot of time (hourS) to return :( >>> on other SGBD (like Firebird) with same amount of data >>> they return immediatly ... >>> >>> Thanks by advance >>> stéphane >>> _______________________________________________ >>> 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --------------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science =========================================================================== _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
