One of the problem that I'm facing with MySQL is choosing the primary key for a few of my tables. I know that this key will be used in the URL so when Google crawls the website it will fetch that specific page with the id in the URL. This means that I should give a good attention to what type of primary key to use.
The easiest solution is to use AI Auto-Increment BigInt, but that doesn't goes very well for scaling. You usually want to put the shard key in the URL, but for right now I don't intend to use sharding, but I want to be ready for it.
3rd party middle-tier solution can handle that by injecting their own unique identifier and make cross joins base on the ID. Another option is to use uuid_short() function on MySQL which generated a unique BigInt number instead of using GUID. GUID looks awful in the URL and I prefer number like Pinterest has done with their pins. Pinterest use the shard Id, data type and random number for their generated ID.
Of course the best thing is to decide which scaling technology to use before deciding which primary key to use. There is an option to generate a unique BigInt number yourself, but it has to be sequential in order to allow you fast inserts so the data will consist in sequential order on the disk and prevent overhead on the indexes. You can use the ticks and calculate it from a particular date, add a random number and the mac address or MySQL server id. Another option is to use a server that generated a unique number but that's a single point of failure.
Another option that I thought about is just use unix time and put random bits at the right side, although not an optimal solution, can't work pretty for some solutions.
Another option is to use snowflake. snowflake is a network service for generating unique ID numbers at high scale. Check this out on github.
Yet another option, you can use the current datetime + user id (auto-increment in database) + random number. Still haven't thought a bout it a lot and waiting to hear your opinion.
I still can't decide which way to go, and I certainly don't want to mass around with canonical URLs and make Google change all the URLs in the future to come. Do make a smart choice when deciding which typr of primary key to use. If you have any suggestions, please comment below.
The easiest solution is to use AI Auto-Increment BigInt, but that doesn't goes very well for scaling. You usually want to put the shard key in the URL, but for right now I don't intend to use sharding, but I want to be ready for it.
3rd party middle-tier solution can handle that by injecting their own unique identifier and make cross joins base on the ID. Another option is to use uuid_short() function on MySQL which generated a unique BigInt number instead of using GUID. GUID looks awful in the URL and I prefer number like Pinterest has done with their pins. Pinterest use the shard Id, data type and random number for their generated ID.
Of course the best thing is to decide which scaling technology to use before deciding which primary key to use. There is an option to generate a unique BigInt number yourself, but it has to be sequential in order to allow you fast inserts so the data will consist in sequential order on the disk and prevent overhead on the indexes. You can use the ticks and calculate it from a particular date, add a random number and the mac address or MySQL server id. Another option is to use a server that generated a unique number but that's a single point of failure.
Another option that I thought about is just use unix time and put random bits at the right side, although not an optimal solution, can't work pretty for some solutions.
Another option is to use snowflake. snowflake is a network service for generating unique ID numbers at high scale. Check this out on github.
Yet another option, you can use the current datetime + user id (auto-increment in database) + random number. Still haven't thought a bout it a lot and waiting to hear your opinion.
I still can't decide which way to go, and I certainly don't want to mass around with canonical URLs and make Google change all the URLs in the future to come. Do make a smart choice when deciding which typr of primary key to use. If you have any suggestions, please comment below.
No comments:
Post a Comment