Magnus Manske <[EMAIL PROTECTED]> wrote: > 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 ) ;
The condition could be written a bit more laconically: pos1 BETWEEN 1 AND 1000000 > On my table (ca. 180K entries) this takes about 10 seconds for the > above, which returns all rows. Since you are not benefitting from the index for this query, you might get better performance by not using it: +pos1 BETWEEN 1 AND 1000000 The unary plus operator suppresses the use of the index on the affected column, while having no effect on the value of the expression. > 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. Having an OR also suppresses the index. > I pasted the EXPLAIN below, but I'm no wiser :-( It usually helps to start with EXPLAIN QUERY PLAN, rather than raw EXPLAIN. The former shows in compact form which tables and indexes are used. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
