> > Is the order of WHERE clauses important as C-language is? > For instance is > > SELECT title FROM tracks > WHERE ((title=:last_title AND id>:last_id) AND title LIKE %Mad% > > better than > > SELECT title FROM tracks > WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) > > ?Anyway, can the index improve select performance in both previous cases? > > Igor Tandetnik ha scritto: > >> "Andrea Galeazzi" <[email protected]> wrote in >> message news:[email protected] >> >> >>> I red this article on wiki: >>> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor >>> I've got a similar case but the difference is that I've to use LIKE >>> operator instead of = >>> SELECT title FROM tracks >>> WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) >>> OR ((title>:last_title)) ORDER BY title,id; >>> id is the primary key and I created an index for (id,title). >>> My question is: will the previous query be actually faster then just >>> only using OFFSET and LIMITS even if I also need a LIKE operator on >>> title column? >>> >>> >> SQLite won't be able to use the index to satisfy LIKE condition. So you >> should concentrate on the other clauses. An index on (title, id) should >> help. For greater effect, change the query to >> >> SELECT title FROM tracks >> WHERE title LIKE %Mad% AND >> title >= :last_title AND >> (title>:last_title OR id>:last_id) >> ORDER BY title,id; >> >> Igor Tandetnik >> >> >> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> __________ NOD32 3939 (20090316) Information __________ >> >> This message was checked by NOD32 antivirus system. >> http://www.eset.com >> >> >> >> >> > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __________ NOD32 3941 (20090317) Information __________ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > >
_______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
