Re: Create index on tables that are joined?

On Oct 16, 1:58 pm, Stick With Hair <StickWithH..._at_gmail.com> wrote:
> No, I have not. I can not create indexes because I am not in the IT
> group but standard procedure (as I know it) is to create indexes on
> all foreign keys anytime tables are joined. The IT group is saying an
> index on ABC will not help because we are returning all the results.
> I'm just looking for some clarification because my theory is that it
> will help but I can't test to confirm.
>
> Stick
>
> On Oct 16, 2:21 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
> > On Oct 16, 12:47 pm, Stick With Hair <StickWithH..._at_gmail.com> wrote:
>
> > > I have a table "ABC" which has 2 million records. A second table "XYZ"
> > > has 10 million records. I am joining ABC to XYZ and want to get all
> > > the records from ABC and only matches in XYZ (left join).
>
> > > ABC --> XYZ
>
> > > On Fields:
> > > CustName
> > > CustTitle
> > > CustPhoneNumber
>
> > > The table XYZ has an index on those 3 fields but ABC does not. If I
> > > created an index on those fields in ABC will it help with query
> > > performance even though I am bringing in all the rows from ABC? I
> > > would guess that we need indexes on all foreign keys in both tables.
>
> > > Any Suggestions or ideas?
> > > Stick with hair
>
> > Have you tried creating such an index on ABC and viewing the query
> > plan?
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

It won't, as it will execute a full table scan to return all rows (the left join guarantees that). We need to see your execution plan for this query; possibly there is some detail you've missed in describing this situation.

David Fitzjarrell Received on Tue Oct 16 2007 - 14:27:01 CDT