Re: Partitioning Approach for multi-tenant db

[Rick James]

The PARTITION feature does none of the things you want.

Do you have any queries that span multiple SiteIDs?

If not, then simply give each Site its own DATABASE. The tables will independently have AUTO_INCREMENT as you request. There will be no performance gain (but PARTITION would not do that either).

To "rightfully declare that each client's data is separated from other clients", give connect to the one DATABASE that the client belongs to.

For the few queries that need cross-database queries, have another login, etc. This will partially "separate" things, but could also open the door to hacking.

You _do_ have an API layer between the users and the database, correct? That layer is your main line of defense for "separating" the clients; the DATABASE separation is secondary.