Matthew Gertner <matthew-SdIEm1mkZcNWk0Htik3J/[EMAIL PROTECTED]> wrote:
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.
Reordering LEFT JOIN changes the meaning of the statement. You don't
want your DBMS to do that to you behind your back. Make sure you know
what you are doing, and that the reordered statement still does what
it's supposed to do. Again, (A LEFT JOIN B) produces different results,
in general, than (B LEFT JOIN A).
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
