Back to General and Gameplay Programming

simple sql question

General and Gameplay Programming Programming

Started by Uthman December 24, 2012 02:55 AM

2 comments, last by LorenzoGatti 11 years, 4 months ago

Uthman

485

Author

December 24, 2012 02:55 AM

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"

Telastyn

3,778

December 24, 2012 05:03 AM

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)

-- Tangent Developer Journal - Delegates - Part 2

PhillipHamlyn

579

December 24, 2012 02:32 PM

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.

LorenzoGatti

4,648

December 25, 2012 07: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?).

Omae Wa Mou Shindeiru

simple sql question

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

simple sql question

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

Reticulating splines