
How to Organize a Database Table’s Keys for Scalability - astrec
http://blog.maxindelicato.com/2008/12/how-to-organize-a-database-tables-keys-for-scalability.html
======
albertni
You would hope that someone writing an introductory guide like this would have
emphasized that it's basically trivial to add new shards for the first two
options, whereas it's a pain, or at least trickier (you could block all
inserts briefly and change the auto-increment offset and current auto-
increment value in each shard), with the third option. Sharding without a plan
to add more shards seems unwise, unless you have a really good idea of what
kind of usage you're expecting in the foreseeable future. It's all discussed
in the comments but personally I think it would have been worth explicit
mention in the post itself.

~~~
newt0311
Having only a single auto-increment column with sharding is likely a bad idea
to begin with. In most DBs, (not MySQL I think), the engine uses a specialized
setup to hand out the next value in line globally so that while the auto-
increment column will be unique, it will be slow. In MySQL, I think they use
some sort of special table and mark the rows as they get used up (I could be
wrong. I don't usually use MsSQL). Thus, on a MySQL DB, it is possible to get
incorrect behavior when changing something in the middle of transactions. So,
adding in shards with auto-increment in MySQL could turn bad very quickly and
very silently.

