Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Questions on Primary keys and indexes

    Hi all,

    The following are questions that I've always wanted to find answers to:

    1) If a column is defined as a Primary Key, is it automatically indexed? Specifically, do we need the second line "INDEX(`id`)" in the example below?

    Code:
    `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    INDEX(`id`),
    2) Do we get the same results with the following two ways of declaring indexes?

    Code:
    /* Indexes are jointly declared */
    `ida` MEDIUMINT(8) UNSIGNED NOT NULL,
    `idb` MEDIUMINT(8) UNSIGNED NOT NULL,
    INDEX(`ida`, `idb`),
    
    /* Indexes are singly declared */
    `ida` MEDIUMINT(8) UNSIGNED NOT NULL,
    `idb` MEDIUMINT(8) UNSIGNED NOT NULL,
    INDEX(`ida`),
    INDEX(`idb`),
    I hope the questions aren't "stupid"

    Many thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1) yes, a PK is automatically indexed

    2) no -- only one index will be used per query, so the separate indexes are not as useful as the composite index when filtering needs to be done on both columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    1) yes, a PK is automatically indexed

    2) no -- only one index will be used per query, so the separate indexes are not as useful as the composite index when filtering needs to be done on both columns
    Many thanks, r937

    With regard to #2, does that mean it's preferable to use a composite index?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    does that mean it's preferable to use a composite index?
    that depends on what results your query expects to return and what search criteria are being used
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    With regard to #2, does that mean it's preferable to use a composite index?
    If you're using both ida and idb in your where clause then it's best to have the composite index. If you're sometimes going to be using ida in the where clause and at other times only idb then it's better to have the two separate indexes. If you always have ida in your where clause and sometimes idb as well then either index will work. You could easily have the best of both worlds and have a composite index on ida, idb and another separate index just on idb.

  6. #6
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Great thanks, mike_bike_kite!

    I didn't know such differences exist - glad I asked

    When you say both ida and idb in the same WHERE clause, do you mean a query such as:

    Code:
    SELECT * FROM some_table WHERE ida=1 AND idb=5;

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, the composite index would be used for that query

    it's like if i ask you to look up a name in the phone book (which is in sequence by last name, first name) -- if i ask you to find all Smiths (which is a lot) whose first name starts with T, you'd use the secondary index to scan ahead through all the Smiths easily

    but if i asked you to find every Smith who lives on Elm street, you'd have to read through all the Smiths (can only use the front part of the index)

    and if i asked you to find everyone who lives on Elm street, you'd have to read through the whole phone book (can't use the index at all)

    so proper indexing is determined by what queries you're going to be running

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by pearl2
    When you say both ida and idb in the same WHERE clause, do you mean a query such as:

    Code:
    SELECT * FROM some_table WHERE ida=1 AND idb=5;

    Yes that's exactly what I mean. Rudy's explanation is pretty good so I'd read that a few times. Oddly if you change the AND in your query to an OR then we'd be back to using 2 indexes again - can you see why?

    Indexing and performance tuning can be quite an in depth subject (but it becomes second nature with enough experience) so I'd concentrate more on just ensuring each table has a primary key. When you do find a query that runs slowly then I'd start to investigate different types of index etc at that point.

  9. #9
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks for the enlightenment, r937 and mike_bike_kite!

    At least now I know declaring indexes jointly (composite) is not the same as declaring them singly - that itself is an important new knowledge for me.

    Does the same concept apply to primary keys? Specifically, the definition "PRIMARY KEY(`ida`, `idb`)" is a composite Primary Key and is different from the two definitions "PRIMARY KEY(`ida`)" and "PRIMARY KEY(`idb`)". Am I on the right track?

  10. #10
    Join Date
    Mar 2004
    Posts
    480
    You can only have one primary key in a table so you can't declare two like in your example. It can be a multi-column key though.
    This is valid:
    PRIMARY KEY(`ida`, `idb`)

    This isn't:
    "PRIMARY KEY(`ida`)" and "PRIMARY KEY(`idb`)

  11. #11
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Great thanks guelphdad!

    Ah, that was a bad one by me

    So does a composite primary key work the same way as a primary key with a single value?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    So does a composite primary key work the same way as a primary key with a single value?
    you mean, a PK with a single column

    yes, it works the same way, but with two columns instead of one



    in essence, you could have repeating values in either of the columns of a composite PK, but the combination of values must be unique

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks, r937!

    Just one more doubt:

    Code:
    PRIMARY KEY('ida', 'idb');
    INDEX ('ida');
    Is the INDEX redundant, since ida is defined as part of a composite PRIMARY KEY?

    Lots of to all of you out there. Thank you so much

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    it's redundant

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    Code:
    PRIMARY KEY('ida', 'idb');
    INDEX ('ida');
    please note, both of those will fail

    you cannot declare a PK or an index on strings!!

    do this instead --
    Code:
    PRIMARY KEY ( ida, idb )
    see the difference?

    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
  •