Jump to content

  • Log In with Google      Sign In   
  • Create Account


simple sql question


Old topic!
Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.

  • You cannot reply to this topic
3 replies to this topic

#1 Uthman   Members   -  Reputation: 480

Like
0Likes
Like

Posted 23 December 2012 - 08:55 PM

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.



"a low level aho master like you couldn't kill me even if I let you"

Sponsor:

#2 Telastyn   Crossbones+   -  Reputation: 3718

Like
1Likes
Like

Posted 23 December 2012 - 11:03 PM

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)

#3 PhillipHamlyn   Members   -  Reputation: 454

Like
1Likes
Like

Posted 24 December 2012 - 08:32 AM

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.



#4 LorenzoGatti   Crossbones+   -  Reputation: 2510

Like
2Likes
Like

Posted 25 December 2012 - 01:57 AM

"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, 25 December 2012 - 02:00 AM.

Produci, consuma, crepa




Old topic!
Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.



PARTNERS