It seems that I yet need help with another query. This one is just too slow. I've included the "explain" and the table schema. I've been using the prepare/step model directly. What should I change on my indexing to make it faster?
The schema: CREATE TEMPORARY TABLE IF NOT EXISTS bounds (bi INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT NULL, bqi INTEGER, bri INTEGER, bqis INTEGER, bris INTEGER); CREATE UNIQUE INDEX IF NOT EXISTS qisris ON bounds (bqis, bris); CREATE UNIQUE INDEX IF NOT EXISTS qiri ON bounds (bqi, bri); insert into bounds values(NULL,1,1,5880,5880); CREATE TABLE results_1 (qi INTEGER, ri INTEGER, drl INTEGER, score INTEGER, qis INTEGER, ris INTEGER); CREATE UNIQUE INDEX loc_1 ON results_1 (qi,ri); The queries (both of these run slow but I care about the second): "select count(*) from results_1 where qi = 5604 OR ri = 5468;" returns 102 So you can see the following query should only be doing a max over a 102 pieces; that's not very many. "explain select qi,ri,drl,max(score) as scr from results_1, bounds where (qi = 5604 OR ri = 5468) AND (qi >= bqis AND qi <= 5604) AND (ri >= bris AND ri <= 5468) AND bi = 1 and qis = bqis AND ris = bris;" 0|MemNull|0|0| 1|MemNull|1|0| 2|MemNull|2|0| 3|MemNull|4|0| 4|MemNull|3|0| 5|Goto|0|73| 6|Integer|1|0| 7|OpenRead|1|2| 8|SetNumColumns|1|5| 9|Integer|0|0| 10|OpenRead|0|6| 11|SetNumColumns|0|6| 12|Integer|0|0| 13|OpenRead|2|8226|keyinfo(2,BINARY,BINARY) 14|Integer|1|0| 15|MustBeInt|1|63| 16|NotExists|1|63| 17|Integer|5604|0| 18|NotNull|-1|21| 19|Pop|1|0| 20|Goto|0|63| 21|MakeRecord|1|0|dd 22|MemStore|6|1| 23|Column|1|3| 24|NotNull|-1|27| 25|Pop|1|0| 26|Goto|0|63| 27|MakeRecord|1|0|dd 28|MoveGe|2|63| 29|MemLoad|6|0| 30|IdxGE|2|63|+ 31|RowKey|2|0| 32|IdxIsNull|1|62| 33|IdxRowid|2|0| 34|MoveGe|0|0| 35|Column|0|0| 36|Integer|5604|0| 37|Eq|100|41|collseq(BINARY) 38|Column|0|1| 39|Integer|5468|0| 40|Ne|356|62|collseq(BINARY) 41|Column|0|1| 42|Column|1|4| 43|Lt|355|62|collseq(BINARY) 44|Column|0|1| 45|Integer|5468|0| 46|Gt|356|62|collseq(BINARY) 47|Column|0|4| 48|Column|1|3| 49|Ne|355|62|collseq(BINARY) 50|Column|0|5| 51|Column|1|4| 52|Ne|355|62|collseq(BINARY) 53|Column|0|3| 54|CollSeq|0|0|collseq(BINARY) 55|AggStep|3|1|max(1) 56|Column|0|0| 57|MemStore|0|1| 58|Column|0|1| 59|MemStore|1|1| 60|Column|0|2| 61|MemStore|2|1| 62|Next|2|29| 63|Close|1|0| 64|Close|0|0| 65|Close|2|0| 66|AggFinal|3|1|max(1) 67|MemLoad|0|0| 68|MemLoad|1|0| 69|MemLoad|2|0| 70|MemLoad|3|0| 71|Callback|4|0| 72|Halt|0|0| 73|Transaction|0|0| 74|VerifyCookie|0|4| 75|Transaction|1|0| 76|VerifyCookie|1|3| 77|Goto|0|6| 78|Noop|0|0| Thanks again for any help. ______________________________ Brannon King ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
