On 9 Dec 2010, at 4:27pm, Vander Clock Stephane wrote: > no one have an idea how to do such query ??
I can't solve your problem but I have observations. I don't see how any SGDB (or RDBS as we call them) could do this quickly without lots of indexes. > x3_y2>=<#randomnumber34> and > x3_y2<=<#randomnumber34> + 20 and In SQLite you can write x3_y2 BETWEEN #randomnumber34 AND (#randomnumber34 + 20) Your long SELECT command is something I would probably do in my programming language instead of SQLite. Just read every record and do the 'ifs' in my language. I think this would actually be as fast or faster than having SQLite do it. But there are ways to make SQLite do it quickly. Either way you have to read every record, so there's no way to save time there. Another way to do it would be to add a column to your HASH table. Let's call it 'tally'. You would start by doing UPDATE HASH set tally = 0; Then you would do 25 UPDATE commands like UPDATE HASH SET tally = tally + 1 WHERE x1_y1 BETWEEN #randomnumber1 AND (#randomnumber1 + 20); [...] UPDATE HASH SET tally = tally + 1 WHERE x3_y2 BETWEEN #randomnumber34 AND (#randomnumber34 + 20); [...] UPDATE HASH SET tally = tally + 1 WHERE x5_y5 BETWEEN #randomnumber73 AND (#randomnumber73 + 20); Then to find the records which obeyed all 25 limits you'd do SELECT id FROM HASH WHERE tally = 25; If you put 26 indexes on the table (one on each x_y and one on tally) this should execute really quickly. It might be faster still if you make another table just for the 'tally' column. The above assumes that a small proportion of the points obeys each restriction. If most points obey most restrictions then you should set tally to 1 at start and to 0 if any restriction is /not/ true. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
