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. 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
