Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Does the order of fields in a multi-field Primary Key matter?

    As simplified as I can make, a User goes through a process to select clients they want to include in 1 or more reports. The process writes records into a SelectedClients table, with only 2 fields: UserID and ClientID.

    Reports can then be run against this group of clients with this SQL:

    SELECT tblClients.* FROM tblClients INNER JOIN tblSelectedClients
    ON tblClients.ClientID = tblSelectedClients.ClientID
    WHERE tblSelectedClients.UserID = [myUserID]

    (Of course, the actual SQL for a specific report will have joins to other tables/queries, but I don't think that's an issue here.)

    So my question is, should my PrimaryKey for tblSelectedClients be UserID/ClientID, or ClientID/UserID, or maybe it doesn't even matter?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,577
    Thanks
    5
    Thanked 1,057 Times in 926 Posts
    As long as neither would ever result in a duplicate primary key it really depends on volume in the query but I'd think you want the inner join to be as efficient as possible. So, I'd pick ClientId/UserId.

    Joe

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I think you may need two indexes, one with clientID as the key and another with UserID as the key. The first will help with the join, the other will help finding the required user through the WHERE clause. This being the case, from this query alone, I don't think it's possible to advise one over the other. If you set the primary key as clientID/userId, you will need to have a second index for UserID, if the other way around, the second index will be needed for ClientID.

    Of course, this may also depend on the indexes you have on tblClients. If you already have an index with ClientID as the first field and another with UserID as the first field, you can simply change the WHERE clause to set the condition on tblClients.UserID (if there is such field) and then set the primary key as ClientID / UserID. In this case, there will be no need for the second index.
    Rui
    -------
    R4

Posting Permissions

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