On Wed, Feb 8, 2012 at 10:57 AM, Timothy Anderson < [email protected]> wrote:
> I'm using sqlite 3.7.10 to collect a message log file--basically one > table, with some metadata (timestamp and message type) plus a blob > containing the raw message; we use this to play sessions back. > For one of the message types (video frames), the blob size is ~100K; for > everything else, no more than a few hundred bytes. > During playback, there is a particular query that is very slow, and I > don't understand why. Help? > > This is fast: > SELECT Timestamp, MessageType, Topic, IsUDP, SourceAddress, SourcePort, > DestAddress, DestPort, Message FROM Messages WHERE (messagetype = > 'VideoFrame'); > This is very slow: > SELECT Timestamp, MessageType, Topic, IsUDP, SourceAddress, SourcePort, > DestAddress, DestPort, Message FROM Messages WHERE (messagetype = > 'VideoFrame') order by timestamp; > > Both the messagetype and timestamp fields are indexed. > I think you want messagetype and timestamp to be indexed together and in that order. Like this: CREATE INDEX whatever ON messages(messagetype, timestamp); > If I use one of the message types that has small blobs, the query is > almost instantaneous; similarly, if I select most of the message types, > including 'VideoFrame', the query is very fast. It's only when the query is > limited to that one message type that it's slow, and then only if it's > sorted. That is, if there is no WHERE clause, or if it's > WHERE (messagetype <> 'NAV') > or > WHERE (messagetype = 'NAV') > then the query is fast, regardless of sorting. > > I have tried changing the page_size to higher values (up to 64K), and > increasing the cache_size, but neither has any effect. > > Thanks for any suggestions. > Tim Anderson > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
