I'm running the following query: SELECT DISTINCT _Resource.ResourceType, _Resource.Id, _Resource.OwnerId, _Resource.Created, _Resource.Modified, _Resource.Name, _Resource.ParentId, _Resource.Version, _Resource.Description, _Resource.Creator, _File.Size, _File.MimeType, _File.OriginURI, _File.OriginMimeType, _File.Hash, _File.ContentRating, _File.OriginalExtension, _File.Thumbnail, _File.Data, _File.TorrentInfoHash, _Resource_Metadata.New, _File_Metadata.IsPublished, _File_Metadata.ReceiveStatus, _File_Metadata.ShareMessageId, _Audio.Length, _Audio.BitRate, _Image.Height, _Image.Width, _Image.ColorsUsed, _Text.Summary, _Video.Height, _Video.Width, _Video.Length, _Video.FrameRate, _Video.DataRate, _Video.SampleSize FROM _File LEFT OUTER JOIN _Resource ON _File.Id=_Resource.Id LEFT OUTER JOIN _Resource_Metadata ON _Resource_Metadata.ParentId=_Resource.Id LEFT OUTER JOIN _File_Metadata ON _File_Metadata.Id=_Resource_Metadata.Id LEFT OUTER JOIN _Audio ON _File.Id=_Audio.Id LEFT OUTER JOIN _Image ON _File.Id=_Image.Id LEFT OUTER JOIN _Text ON _File.Id=_Text.Id LEFT OUTER JOIN _Video ON _File.Id=_Video.Id LEFT OUTER JOIN _Source ON _Source.ParentId=_Resource_Metadata.Id LEFT OUTER JOIN _Source_PeerCommonName ON _Source_PeerCommonName.ResourceId=_Source.Id WHERE _Source_PeerCommonName.Value=? AND _File_Metadata.Id IN (SELECT Id FROM _File_Metadata WHERE ReceiveStatus=?)
EXPLAIN QUERY PLAN tells me that a full scan is being performed on the _File table, which is the first in the FROM clause. When I reorder the joins so that _File_Metadata is first in the FROM clause, then its primary key index (on Id) is used, as expected (since Id is used in the WHERE clause). I would have expected SQLite's query optimizer to reorder the joins automatically to use an index rather than a full table scan. Am I doing something wrong or do I have to reorder the joins myself to optimize index usage? -- View this message in context: http://www.nabble.com/Query-optimization-tf4724113.html#a13506536 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
