Sign in to follow this  
Uthman

simple sql question

Recommended Posts

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.

Share this post


Link to post
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)

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this