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)


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! Same question at stackoverflow with more answers

  • Please link to the outer place where you asked the same question.
    – Rick James
    Commented May 29, 2016 at 1:05

1 Answer 1


The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key.

In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.