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, _File_Metadata.DownloadProgress, _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 INNER JOIN _Resource ON _File.Id=_Resource.Id INNER JOIN _Resource_Metadata ON _Resource_Metadata.ParentId=_Resource.Id INNER 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 WHERE _File_Metadata.Id IN (SELECT Id FROM _File_Metadata WHERE ReceiveStatus<>? AND ReceiveStatus<>?)
SQLite does a full scan on the _File table according to EXPLAIN QUERY PLAN. If I move _File_Metadata to the front of the FROM clause, it starts by using the _File_Metadata primary key index as expected. If I remove the outer joins from the query, the inner joins are reordered automatically. Shouldn't SQLite be able to reorder the inner joins automatically even if they are followed in the FROM clause by outer joins? Matt -- View this message in context: http://www.nabble.com/Another-query-optimization-question-tf4732323.html#a13531761 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
