It is now accepted that the use of self-incrementing values as a primary key has several drawbacks when this technique is misused. This is why the use of UUIDs is now becoming more widespread. However, not all databases treat this type of key in the same way and this can affect the performance of our queries. This is especially the case if you use MySQL.
Titouan GALOPIN recently wrote a post on the use of UUIDs in Symfony with Doctrine. He recommends in this post to use jointly an auto-generated identifier that will be used by the database and a UUID that will serve as an entry point for the data of our application.
However, MySQL is beginning to implement solutions to manage performance issues related to the use of UUIDs. For example, MySQL version 8 introduces three new functions in this regard: IS_UUID, UUID_TO_BIN and BIN_TO_UUID.
MySQL performance management does not support UUIDs and we usually store the UUID in its text form, which is a 36-character string. This is essentially why it causes storage and performance problems because the data is more complex and more cumbersome to manage than a simple integer. The UUID_TO_BIN and BIN_TO_UUID functions allow to convert a textual UUID into its binary form (corresponding to the VARBINARY(16) type). This makes the data half the size of the textual UUID, and the binary data is better understood by the database system.
The disadvantage of this solution is that in order to be able to view the UUID in base, it will be necessary to apply the BIN_TO_UUID function to make it readable. In an article on UUID storage, MySQL duplicates the data with a text version to make the data visible, but in the end it comes back to a solution similar to the one mentioned by Titouan. The question is, do you really need to have this text version?
If you work with PHP, you will certainly use the ramsey/uuid library to manage your UUIDs. The latter also provides a ramsey/uuid-doctrine component to interface with Doctrine. It is interesting to note that the latter provides a uuid_binary type using the techniques mentioned in this post.
MySQL or SQL Server?
Comparison of the two database management software, especially on licensing cost, performance and security. An objective analysis to put an end to preconceived ideas.
Performance: MySQL advantage
In terms of pure performance, MySQL is the best, mainly because of its default table format, MyISAM. MyISAM databases are very compact on disk and require very little memory and processor cycles. MySQL can run on Windows without problems but gives better performance on UNIX or UNIX-like systems. You can get even better performance using MySQL on a 64-bit processor (for example, one of those fabulous SPARCstations), because MySQL uses a large number of 64-bit integers internally. Most of the popular Yahoo! Finance portal uses MySQL as its backend database.
As I said before, MySQL offers a wide range of table formats, but in general https://www.enteros.com/products/ these custom choices result in a greater use of resources than MyISAM. However, these other table formats generally provide additional functionality. For example, Berkeley DB supports transactions and actually offers better performance with indexed fields than MyISAM.
When it comes to performance, SQL Server's strength (providing far more functionality than its competitors) is also its weakness. True, most of these features are designed to optimize performance, but getting a feature-rich environment also means making sacrifices elsewhere. In this case, this translates into increased complexity, disk storage and memory requirements, and decreased performance. If you can't afford to support SQL Server with powerful hardware and solid experience, you should definitely switch to another DBMS, or you will be disappointed with the results.
It is worth noting that both systems will work perfectly in a .NET or J2EE architecture. Also, both will benefit from RAID technology and will perform better if the data storage space resides on a hard drive or array dedicated solely to this function.