CODEWITHSUNDEEP
mysqldatabase-designrelational-databasedatabase-schemasaas
andymcgregor
andymcgregor

Reputation: 1015

Composite Primary Keys and auto increment? What is a good practices?

I'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like PK(client_id, id). I have 2 ways here,

1: increment "id" by myself (by trigger or from code)

or

2: Make "id" as auto-increment by mysql.

In first case i will have unique id's for each client, so each client will have id 1, 2, 3 etc.. In second case id's will grow for all clients. What is the best practice here? My priorities are: performace, security & scaling. Thanks!

Upvotes: 4

Views: 1648

Answers (2)

Rick James
Rick James

Reputation: 142238

"PK(client_id, id)" --

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(client_id, id),
INDEX(id)

Yes, that combination will work. And it will work efficiently. It will not assign 1,2,3 to each client, but that should not matter. Instead, consecutive ids will be scattered among the clients.

Probably all of your queries will include WHERE client_id = (constant), correct? That means that PRIMARY KEY(client_id, id) will always be used and INDEX(id) won't be used except for satisfying AUTO_INCREMENT.

Furthermore, that PK will be more efficient than having INDEX(client_id, id). (This is because InnoDB "clusters" the PK with the data.)

Upvotes: 1

O. Jones
O. Jones

Reputation: 108641

You definitely want to use autoincrementing id values as primary keys. There happen to be many reasons for this. Here are some.

  1. Avoiding race conditions (accidental id duplication) requires great care if you generate them yourself. Spend that mental energy -- development, QA, operations -- on making your SaaS excellent instead of reinventing the flat tire on primary keys.
  2. You can still put an index on (client_id, id) even if it isn't the PK.
  3. Your JOIN operations will be easier to write, test, and maintain.

  4. This query pattern is great for getting the latest row for each client from a table. It performs very well. It's harder to do this kind of thing if you generate your own pks.

        SELECT t.*
      FROM table t
      JOIN (SELECT MAX(id) id 
              FROM table 
             GROUP BY client_id
          ) m ON t.id = m.id

Upvotes: 3

Related Questions