Samuel R. Neff wrote:
Will the original poster still run into performance problems where sqlite
will only use one index per table so if targetid matches on a ton of rows
sqlite has to scan them all for the matching sourceid? Perhaps a
multi-column index would be appropriate here to index both "target.id" and
"source.id" in the same index.
Samuel,
I don't think there will be a problem since each select in the union is
free to use its own index for the Link table lookup.
A compound index would not help.They are only useful where you want to
match one or more fields in the index. A compound index on (Target.Id,
Source.Id) could not be used to locate a record given a source id
without scanning every index entry since the source id one could be
associated with any target id.
If the union query does have a problem with a single index, then the
queries could be turned around so they scan the link table and then use
the index on the entity id field to locate the associated entity. This
might even be faster in general, depending upon the relative sizes of
the link and entity tables.
Select ne.*
From Link AS l
Join Entity AS ne on l."Source.Id" = ne.Id
Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
UNION
Select ne.*
From Link AS l
Join Entity AS ne on l."Target.Id" = ne.Id
Where l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
