Followers 0

# simple sql question

## 3 posts in this topic

Q1: Regarding indexing foreign keys on InnoDB

Is this required? Is it a best practice?

Q2: If the answer to Q1 is Yes, what about if I have two FK's and I only ever reference them at the same time--should I create individual indices along with the group index, or would the group index suffice?

EG: Table name = Object

Object has two FK's: User1, User2

I only ever access the table while searching using WHERE User1='' AND User2=''.

Any incites from gd's sql gurus would be much appreciated and ++thanks in advance.

0

##### Share on other sites
I don't know about *required*, but yes you should always do it.

No, if you have a paired key you only need to index the pair (that said, if you're indexing table relations off of data rather than ID... Have a really good reason)
1

##### Share on other sites

Depends on whether you are mostly reading from the DB (in which case, put indexes that cover every Where clause) or the DB has quite a lot of writes (in which case you need to be a little more discerning).

However; on balance, the DB is there to be read - you will read it much more often that write to it, so generate indexes that cover all your Where clauses, and only remove them if you find they are not used.

Also, generally dont bother indexing a table that will have < 100 rows; most optimisers wont use the index.

1

##### Share on other sites
"I only ever access the table while searching using WHERE User1='' AND User2=''."
In this case you want an index on User1 and User2 (combined, not one on user1 and one on user2).
A clustered index would be great, but maybe you need it for the primary key (do you have one? What is it?). Edited by LorenzoGatti
2

## Create an account

Register a new account