Hi everybody,
I have thought a bit about sharding tables, since partitioning cannot be done with foreign keys in a mySQL table. Maybe there's an option to switch to a different relational database that features both, but I don't see that as an option right now.
So, the sharding idea seems like a pretty decent thing. But, what's a good approach to do this on a application level?
I am guessing that a take-off point would be to prefix tables with a max value for the primary key in each table. Something like products_4000000 , products_8000000 and products_12000000. Then the application would have to check with a simple if-statement the size of the id (PK) that will be requested is smaller then four, eight or twelve million before doing any actual database calls.
So, is this a step in the right direction or are we doing something really stupid?
Update:
Consistent hashing might also be an idea to share rows between multiple tables based on a primary key or similar.
Another option might be to introduce the sharding at database level. I do not know enough about triggers/views or whatever is used to make this happen.