will indices speed up joins 1

[iwease]

Suppose I have the following situation

table 1
col1: Table1ID INT Primary key
col2: FK_Table2ID INT

table 2
col1: Table2ID INT Primary key

col1 in tables 1 and 2 are the primary keys so they are already indexed. If I want to join table 1 to table 2 (on FK_Table2ID = Table2ID), will having FK_Table2ID indexed make any different in terms of speed? For some reason, I'm thinking that it would only make a difference if I wanted to view the information for the join ordered by FK_Table2ID but maybe this is wrong.

Thanks in advance

 

[Crowley16]

it will definitely make a difference, irrespective of what ordering...

--------------------
Procrastinate Now!

 

[monksnake]

If you create the correct indices, then yes, the index will use the columns specified in the ON clause for joins.

UNLESS it is a cross join

<.

 

[iwease]

ok, good to know. In the past, I just automatically indexed columns I would often be using for joins, but this time, I questioned the idea. Good to hear that I wasn't on the wrong track before. Thanks

 

[Crowley16]

Just as a matter of interest, what's the general considerations that everyone does to decide wether to index or not?

e.g. for me, from most to least important:

1) Primary Key
2) Foreign Key
3) Highly Selective Data
4) Often Searched Data
5) Static Data

although 3-5 is pretty changeable, whereas 1 & 2 are pretty much as standard...



--------------------
Procrastinate Now!

 

[Lamprey13]

Depending on your system, I'd also rate Natural Key as important as your Primary Key.

 

[ESquared]

Just don't get carried away with natural keys.

My wife lived in the US for more than two years before getting a social security number. And the law says that financial companies cannot deny her services simply because she doesn't have one. So it could actually be, in the long run, illegal for a company to use a database that depends on "natural keys" such as SSNs as unique primary keys.

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.