Hi, I'm new to the list, but have been an enthusiastic sqlite user for years.
I've found the strangest issue today. I have a sqlite3 database that contains the following table: CREATE TABLE MAL9_single ( read_name VARCHAR[32], pos1 INTEGER, seq1 VARCHAR[64] ); CREATE INDEX MAL9_sin_index ON MAL9_single ( pos1 ); >From Perl, I query for all rows in that table within a certain range: SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 1000000 ) ; On my table (ca. 180K entries) this takes about 10 seconds for the above, which returns all rows. However, when I repeat the exact same condition with OR: SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 1000000 ) OR ( pos1 >= 1 AND pos1 <= 1000000 ) ; it is about 10 times faster. Adding more OR repeats doesn't speed it up further, sadly ;-) I pasted the EXPLAIN below, but I'm no wiser :-( I don't manage the system here, so I'm rather in the dark about the exact version number. Any ideas? Magnus sqlite> EXPLAIN SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 100 ) ; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 EXPLAIN SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 100 ) ; 00 1 Goto 0 26 0 00 2 OpenRead 0 69 0 00 3 SetNumColumns 0 3 0 00 4 OpenRead 1 260893 0 keyinfo(1,BINARY) 00 5 SetNumColumns 1 2 0 00 6 Integer 100 2 0 00 7 IsNull 2 23 0 00 8 MakeRecord 2 1 1 db 00 9 Integer 1 2 0 00 10 IsNull 2 23 0 00 11 MakeRecord 2 1 5 db 00 12 MoveGe 1 23 5 00 13 IdxGE 1 23 1 01 14 Column 1 0 5 00 15 IsNull 5 22 0 00 16 IdxRowid 1 5 0 00 17 MoveGe 0 0 5 00 18 Column 0 0 6 00 19 Column 1 0 7 00 20 Column 0 2 8 00 21 ResultRow 6 3 0 00 22 Next 1 13 0 00 23 Close 0 0 0 00 24 Close 1 0 0 00 25 Halt 0 0 0 00 26 Transaction 0 0 0 00 27 VerifyCookie 0 138 0 00 28 TableLock 0 69 0 MAL9_single 00 29 Goto 0 2 0 00 sqlite> EXPLAIN SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 100 ) OR ( pos1 >= 1 AND pos1 <= 100 ); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 EXPLAIN SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 100 ) OR ( pos1 >= 1 AND pos1 <= 100 ); 00 1 Goto 0 24 0 00 2 OpenRead 0 69 0 00 3 SetNumColumns 0 3 0 00 4 Rewind 0 22 0 00 5 Column 0 1 1 00 6 Integer 1 2 0 00 7 Lt 2 11 1 collseq(BINARY) 6c 8 Column 0 1 2 00 9 Integer 100 1 0 00 10 Le 1 17 2 collseq(BINARY) 64 11 Column 0 1 1 00 12 Integer 1 2 0 00 13 Lt 2 21 1 collseq(BINARY) 6c 14 Column 0 1 2 00 15 Integer 100 1 0 00 16 Gt 1 21 2 collseq(BINARY) 6c 17 Column 0 0 3 00 18 Column 0 1 4 00 19 Column 0 2 5 00 20 ResultRow 3 3 0 00 21 Next 0 5 0 00 22 Close 0 0 0 00 23 Halt 0 0 0 00 24 Transaction 0 0 0 00 25 VerifyCookie 0 138 0 00 26 TableLock 0 69 0 MAL9_single 00 27 Goto 0 2 0 00 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
