Ive read a few articles on this subject and just wanted to get some clarificiation regarding using random primary keys instead of auto increment. I am building an application and instead of using auto-increment primary keys, I am using the following code to generate a random primary key that includes both numbers and letters:
$bytes = random_bytes(8);
$bytes = bin2hex($bytes);
The reason I would like to use random primary keys is to prevent url tampering. My application also has record level security but I wanted random primary key as a secondary measure. Also, I have this column set to unique as to prevent any rare collisions. If there is a collision i just throw on on screen error asking using to resubmit the form which generates another random key. So my questions are as follows:
Thank you. Again, i know there are other articles out there but many of them are outdated (prior to php 7 random bytes) so I thought id get a fresh take on whether or not random primary keys are considered best practice (why or why not). Thanks.
Bad -- very bad, in fact. Random primary keys play havoc with MySQL's page cache, which will make performance poor under load. It also means some SQL features will be unavailable to you, like multiple-row INSERT.
If you want to protect the ID in a URL from tampering, consider including a keyed hash (e.g, HMAC) of the identifier in the URL.