Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Indexing a foriegn key.

    1. I have a table called companies which has a foriegn key called countryid. countryid references the primary key of table countries.

    table countries has the following fields:

    countryid - primary key auto increment
    countryname

    The table Companies is regulary searched on countryid. Would you therefore recomend indexing this field given the frequent searches it requires or would you simply redesign the query and do an inner join on the countries table:

    e.g a search based on a non indexed countryid field

    Code:
    Select * from Companies where countryid = 1;
    or

    Code:
    Select * from companies 
    inner join countries as c on
    	c.countryid = companies.countryid
    Where
    	c.countryid = 1;
    Since the second example passes the condtion to the countries table and searches on the primary key would this be more faster than searching on a non indexed field as in the first example or would you simply index the countryid in the companies table and use the first example? My minds boggeling!

    2. I have another scenario aswell.

    The companies table has the following fields:

    companyid - primary key auto increment
    userid - foreign key references primary key from table users

    Again this table is frequently searched by users by using their userid to list all companies assigned to them. Would you therefore recommend indexing the userid or redesign the query using an inner join.

    e.g a search based on a non indexed userid field:

    Code:
    Select * from companies where userid = 1;
    or

    Code:
    Select * from companies 
    inner join users as u on
    	u.userid = companies.userid
    Where
    	u.userid = 1;

    Alternativeley would you make the userid in the companies table into a compound primary key with the companyid. In which case the first example would be doing a search on an indexed field in any case?

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    If you don't need the information from the countries table then don't do a join. Similarly for joining the users table. it's that simple...
    Doing a join is ALWAYS going to be slower than not doing a join.

  3. #3
    Join Date
    Apr 2007
    Posts
    6

    Thumbs up

    I thought that when you created a foreign key constraint an index was built up in the background anyway.
    In 4.0 You have to index the column before you create the FK constraint.

    Are their performance issues with these, or are you just taking the precautions?

    What version of mySQL are you using?

    Hope I helped!
    Regards

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by aschk
    If you don't need the information from the countries table then don't do a join. Similarly for joining the users table. it's that simple...
    Doing a join is ALWAYS going to be slower than not doing a join.
    So what you are saying is index the foreign key in the companies table? Is it correct that whne you create a foriegn key contraint it is automatically indexed as mentioned by dareet?

  5. #5
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by Dareet
    I thought that when you created a foreign key constraint an index was built up in the background anyway.
    In 4.0 You have to index the column before you create the FK constraint.

    Are their performance issues with these, or are you just taking the precautions?

    What version of mySQL are you using?

    Hope I helped!
    Regards
    Am using version 5.0 however theres nothing in my table defs to indicate an index on this field? All i have is the following:

    Code:
    constrain country_fk foreign key (countryid) references tbl_countries(countryid)
    Code:
    constrain users_fk foreign key (userid) references tbl_users(userid)
    I am looking at both performance issues and precautions aswell

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii
    Is it correct that whne you create a foriegn key contraint it is automatically indexed as mentioned by dareet?
    no, it is not

    you have to create the index yourself, before innodb will recognixe the FK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •