On Mon, 2008-08-18 at 11:16 -0600, Dennis Cote wrote: > Does this query run faster? > > select * from mapelements > where ID in > ( > select ID from mapelements_rtree > where mapelements_rtree.NELAT>=79000000 > and mapelements_rtree.SWLAT<=80000000 > and mapelements_rtree.NELON>=79000000 > and mapelements_rtree.SWLON<=80000000 > );
Yes; this query terminates in less than a second. Technically it's not quite the same thing (doesn't return the rtree columns), but it's what I want :-) Interestingly, the original query is extremely compute-bound, there is almost no disk activity! Looking at the output opcodes from the queries, I can't see any significant difference. Though I have to admit I'm by far no expert in vmdb opcodes... sqlite> explain select * from mapelements,mapelements_rtree where mapelements_rtree.NELAT>=79000000 and mapelements_rtree.SWLAT<=80000000 and mapelements_rtree.NELON>=79000000 and mapelements_rtree.SWLON<=80000000 and mapelements.ID=+mapelements_rtree.ID; 0|Trace|0|0|0|explain select * from mapelements,mapelements_rtree where mapelements_rtree.NELAT>=79000000 and mapelements_rtree.SWLAT<=80000000 and mapelements_rtree.NELON>=79000000 and mapelements_rtree.SWLON<=80000000 and mapelements.ID=+mapelements_rtree.ID;|00| 1|Integer|79000000|1|0||00| 2|Integer|80000000|2|0||00| 3|Integer|79000000|3|0||00| 4|Integer|80000000|4|0||00| 5|Goto|0|42|0||00| 6|SetNumColumns|0|11|0||00| 7|OpenRead|0|2|0||00| 8|VOpen|1|0|0|vtab:870B6C0:184B20|00| 9|Rewind|0|39|0||00| 10|SCopy|1|7|0||00| 11|SCopy|2|8|0||00| 12|SCopy|3|9|0||00| 13|SCopy|4|10|0||00| 14|Integer|2|5|0||00| 15|Integer|4|6|0||00| 16|VFilter|1|38|5|DbBaDdBc|00| 17|Rowid|0|11|0||00| 18|VColumn|1|0|12||00| 19|Ne|12|37|11|collseq(BINARY)|6c| 20|Rowid|0|13|0||00| 21|Column|0|1|14||00| 22|Column|0|2|15||00| 23|Column|0|3|16||00| 24|Column|0|4|17||00| 25|Column|0|5|18||00| 26|Column|0|6|19||00| 27|Column|0|7|20||00| 28|Column|0|8|21||00| 29|Column|0|9|22||00| 30|Column|0|10|23||00| 31|VColumn|1|0|24||00| 32|VColumn|1|1|25||00| 33|VColumn|1|2|26||00| 34|VColumn|1|3|27||00| 35|VColumn|1|4|28||00| 36|ResultRow|13|16|0||00| 37|VNext|1|17|0||00| 38|Next|0|10|0||00| 39|Close|0|0|0||00| 40|Close|1|0|0||00| 41|Halt|0|0|0||00| 42|Transaction|0|0|0||00| 43|VerifyCookie|0|17|0||00| 44|TableLock|0|2|0|mapelements|00| 45|Goto|0|6|0||00| sqlite> explain select * from mapelements where ID in (select ID from mapelements_rtree where mapelements_rtree.NELAT>=79000000 and mapelements_rtree.SWLAT<=80000000 and mapelements_rtree.NELON>=79000000 and mapelements_rtree.SWLON<=80000000); 0|Trace|0|0|0|explain select * from mapelements where ID in (select ID from mapelements_rtree where mapelements_rtree.NELAT>=79000000 and mapelements_rtree.SWLAT<=80000000 and mapelements_rtree.NELON>=79000000 and mapelements_rtree.SWLON<=80000000);|00| 1|Goto|0|44|0||00| 2|SetNumColumns|0|11|0||00| 3|OpenRead|0|2|0||00| 4|If|2|24|0||00| 5|Integer|1|2|0||00| 6|OpenEphemeral|3|1|0|keyinfo(1,BINARY)|00| 7|Integer|79000000|3|0||00| 8|Integer|80000000|4|0||00| 9|Integer|79000000|5|0||00| 10|Integer|80000000|6|0||00| 11|VOpen|1|0|0|vtab:80BA750:184B20|00| 12|SCopy|3|9|0||00| 13|SCopy|4|10|0||00| 14|SCopy|5|11|0||00| 15|SCopy|6|12|0||00| 16|Integer|2|7|0||00| 17|Integer|4|8|0||00| 18|VFilter|1|23|7|DbBaDdBc|00| 19|VColumn|1|0|13||00| 20|MakeRecord|13|1|14|c|00| 21|IdxInsert|3|14|0||00| 22|VNext|1|19|0||00| 23|Close|1|0|0||00| 24|Rewind|3|42|0||00| 25|Column|3|0|1||00| 26|IsNull|1|41|0||00| 27|MustBeInt|1|41|0||00| 28|NotExists|0|41|1||00| 29|Rowid|0|15|0||00| 30|Column|0|1|16||00| 31|Column|0|2|17||00| 32|Column|0|3|18||00| 33|Column|0|4|19||00| 34|Column|0|5|20||00| 35|Column|0|6|21||00| 36|Column|0|7|22||00| 37|Column|0|8|23||00| 38|Column|0|9|24||00| 39|Column|0|10|25||00| 40|ResultRow|15|11|0||00| 41|Next|3|25|0||00| 42|Close|0|0|0||00| 43|Halt|0|0|0||00| 44|Transaction|0|0|0||00| 45|VerifyCookie|0|17|0||00| 46|TableLock|0|2|0|mapelements|00| 47|Goto|0|2|0||00| Thanks, Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
