Terrific!
sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND +type=22
ORDER BY eid DESC LIMIT 1;
3261891|4910298|1206924|1|22|9|4
CPU Time: user 0.002000 sys 0.017997
Now I have a workaround - thanks to everyone for the help. I assume
that what sqlite is doing under the hood here is doing a linear search
of rows in descending order (of the primary key eid) starting at
3261976 to find one where type=22. In the common case, it will find
one nearby, and the query will be fast. If it has to search a long
way, though, I'd expect it to be rather slow. Of course, I'm much
better off than before being that at least common cases are fast, but
I'm curious about the general case, where you really want to do an
indexed lookup on type.
Is it a problem in sqlite that it will only optimize: "WHERE
primary_key<=X ORDER BY primary_key DESC" if it's not using an index?
Is it supposed to?
Thanks,
Jeff
On Jul 1, 2008, at 9:56 AM, Noah Hart wrote:
> Jeff, try this select instead
>
> sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE
> eid<=32619760 and +type=22 ORDER BY eid DESC LIMIT 1;
> 0|0|TABLE events USING PRIMARY KEY ORDER BY
>
>
> Regards-- Noah
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
> Sent: Tuesday, July 01, 2008 9:44 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Index and ORDER BY
>
> I agree. If I drop indices that use "type", I get my performance back
> for this query:
>
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22
> ORDER BY eid DESC LIMIT 1;
> 16643833|27906245|5972704|0|22|9|4
> CPU Time: user 0.001000 sys 0.001000
>
> sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE
> eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1;
> 0|0|TABLE events USING PRIMARY KEY ORDER BY
>
> The problem is that indexing the type column gives me a huge
> performance benefit for other queries in my application. Is there any
> way I can force sqlite to not use an index for a particular query?
> Thanks for your help!
> Jeff
>
>
>
> CONFIDENTIALITY NOTICE:
> This message may contain confidential and/or privileged information.
> If you are not the addressee or authorized to receive this for the
> addressee, you must not use, copy, disclose, or take any action
> based on this message or any information herein. If you have
> received this message in error, please advise the sender immediately
> by reply e-mail and delete this message. Thank you for your
> cooperation.
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
