Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    May 2012
    Posts
    89
    Thanks
    51
    Thanked 0 Times in 0 Posts

    PK over three columns, or new column for PK?

    Gidday

    I have a table of 5 columns. The first three are together unique, so they could be the primary key. Or, I could create an auto incrementing id column as the PK.

    My question is - would it be faster to make a query on the three column PK, or on the single ID column PK? And how would that 3 column query look - like this...?

    Code:
    SELECT * FROM mytable WHERE col1 = 'blah' AND col2 = 'blahblah' AND col3 = 'blahblahblah'
    I ask, as I see a lot of posts around saying 'why'd you make a new id column when you could use the unique columns as the PK?'

    The other thing is, I'll be referencing rows in this table from other tables, so I'm thinking it's better so have an id column that I can refer to in the other tables.

    Thanks guys.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    As a general rule, you should not use an autoincrement column for a PK if a natural PK exists. There can be exceptions. For example, if the table in question will then be referenced via foreign key by some other table, it might make sense to have the autonumber column for compactness and efficiency of the foreign key.

    Even if that is true, though, there's no reason you couldn't create a UNIQUE index on the 3 columns, and there are many reasons to do so. With MySQL, there's not too much difference between a PRIMARY key and a UNIQUE index (not true in other DBs; in SQL Serve, the PRIMARY key actually influences strongly how the table is stored on disk).

    But caution: With MySQL, composite keys have some nasty limitations.

    An example:
    Code:
    CREATE TABLE foo (
        fieldA varchar(20),
        fieldB datetime, 
        fieldC int,
        PRIMARY KEY (fieldA, fieldB, fieldC)
    );
    If you were to then do the following queries, MySQL *would*use the primary key:
    Code:
    SELECT ... WHERE fieldA='x' AND fieldB = '2013-1-1' AND fieldC=7
    SELECT ... WHERE fieldA='x' AND fieldB = '2013-1-1'
    SELECT ... WHERE fieldA='x'
    But if you did any of the following, MySQL *CAN NOT* use the primary key to speed queries:
    Code:
    SELECT ... WHERE fieldB = '2013-1-1' AND fieldC=7
    SELECT ... WHERE fieldC=7
    And if you did this query, MySQL would only use the PK for fieldA, *not* for fieldC:
    Code:
    SELECT ... WHERE fieldA='x' AND fieldC=7
    All this is because MySQL can *ONLY* use the fields in the composite key in left-to-right order, without skiipping any.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    shaunthomson (03-31-2013)

  • #3
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,454
    Thanks
    0
    Thanked 632 Times in 622 Posts
    Quote Originally Posted by Old Pedant View Post
    All this is because MySQL can *ONLY* use the fields in the composite key in left-to-right order, without skiipping any.
    But there's nothing to stop you making an additional key on those fields if you are going to need to access by fieldA/C as well as by fieldA/B
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    shaunthomson (03-31-2013)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Quote Originally Posted by felgall View Post
    But there's nothing to stop you making an additional key on those fields if you are going to need to access by fieldA/C as well as by fieldA/B
    Absolutely!

    But then the question arises as to whether you are better off with a single compound index or three separate indexes or the compound key plus one index or...

    And with MySQL, there's no good answer to that except to try variations and benchmark them in your actual situation. And then you also have the fun of learning that sometimes MySQL will purposely choose not to use an available index because it thinks a full table scan will be more efficient. And sometimes MySQL is right and sometimes it is not. Luckily, there are ways to gently guide MySQL to the best answer, but it takes experimentation to know just what is the best answer.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    shaunthomson (03-31-2013)

  • #5
    New Coder
    Join Date
    May 2012
    Posts
    89
    Thanks
    51
    Thanked 0 Times in 0 Posts
    Thanks guys.

    I'm referencing that table in a tagmap table, plus a couple other tables, so I'll go with the AI column, but also have a unique index over those other three columns, and be mindful my queries on those columns go left to right without skipping any columns (and add separate indexes for queries that do need to start elsewhere than the leftmost composite key column).

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Sound like you have it fully covered, than. If you ever have any doubt, though, just use MySQL EXPLAIN command.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    shaunthomson (04-04-2013)

  • #7
    New Coder
    Join Date
    May 2012
    Posts
    89
    Thanks
    51
    Thanked 0 Times in 0 Posts
    Cheers Old Pedant

    I'd never looked at EXPLAIN before.

    Found a good starter here:

    http://phpmaster.com/using-explain-t...mysql-queries/


  •  

    Posting Permissions

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