Is the order of WHERE clauses important as C-language is?
For instance is
SELECT title FROM tracks
WHERE title ((title=:last_title AND id>:last_id) AND 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
