#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    1
    Rep Power
    0

    Foreign Key Index question


    Hi,
    Probably a silly question. When I create a foreign key on a table, I note an index is created on the foreign key column.

    If I use that same column for other querying/searches, is it necessary to create a seperate index on that same column, or is the foreign key index already serving the purpose?

    Thanks,
    John.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    The created index will be used.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    Originally Posted by johnsg6977
    When I create a foreign key on a table, I note an index is created on the foreign key column.
    No, it's not:

    Originally Posted by http://www.postgresql.org/docs/9.3/static/ddl-constraints.html#DDL-CONSTRAINTS-FK

    [...] declaration of a foreign key constraint does not automatically create an index on the referencing columns.


    Originally Posted by johnsg6977
    If I use that same column for other querying/searches, is it necessary to create a seperate index on that same column, or is the foreign key index already serving the purpose?
    Apart from the fact that PostgreSQL does not automatically create that index: It depends on the concrete query. There are many different types of indexes for different purposes. You may be able to use the same index for a foreign key constraint as well as a particular query. But there's no guarantee for it, and it may not be optimal.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo