Mysql Sharding. Application Layer vs Mysql Layer

Question

I am going to create shards in mysql table and I am stuck at a dileman whether to go for application layer sharding or let MySQL handle it. I mean, wether I should be figuring out which shard to go and query at application layer or pass on the query to the MySQL driver and let it decide.

I read about some pros and cons of both here but I am unable to come up with a conclusion.

My personal opinion is to go for the MySQL driver handling it. Open for discussion on this.

Please share your views/experiences.

Answer 1

Sharding does not speed up searching.

Sharding is useful when you hit a write-scaling limit. Most social media apps end up with some form of sharding to handle many thousands of writes per second.

MySQL has no sharding capability. However, add-ons, such as Spider and various Proxies can help.

Or you can build it into the application layer. In this case, each client must first figure out which database shard to talk to, then talk only to that one. This assumes all the activity can be handled in a single shard.

Sure, the application layer may make use of database tables to keep track of what shards exist, and which item is in which shard.