mysql uuid vs auto increment performance

In your analysis, the 25% degradation of the single select, however, does seem to be significant. My view of this may differ somewhat/ The issue is never with the insert statements those will always be similar due to the very slight difference in the amount of data being written. Thank you Ben for your interesting quantitative analysis. Extend testing further to also compare using sequential GUIDs and the bigint.Merge data generated from different systems then using a GUID column as your keyĪllows you to not have to worry about resolving any duplicates when you merge theįinally, as with any performance testing, you should always test with your ownĭata to ensure you are seeing the expected results before making any changes in You the added benefit of uniqueness across systems. One thing to also note is that the GUID column also gives This simple test shows that you achieve pretty much identical performance between Assuming you are running regular maintenance on your indexes

With the indexes rebuilt we now see almost no performance difference between See if the performance has changed at all from our initial test. Also notice that even though we recovered quiteĪ bit of space from index on the guid column it is still using a fair bit more than We can see from these results there is no longer any index fragmentation with With these indexes rebuilt let's again take a look at the storage and fragmentation Let's rebuild each index using T-SQL belowĪnd see if anything changes from these initial results.Īlter index PK_testtabl_357D0D3E194A02AE on testtable_int rebuild Īlter index PK_testtabl_357D0D3E65B8D323 on testtable_guid rebuild With only the SELECT performance of the integer datatype showing any noticeableĮdge over the GUID datatype. Both indexes have very similar performance SELECT * FROM testtable_int WHERE col1=4789341 Generated when you ran the insert script above. Update the GUID in the SELECT, UPDATE and DELETE statements with values that were Note that if you use the T-SQL below you'll need to I also ran a DBCC DROPCLEANBUFFERSīefore each execution to simulate the worst case scenario of the data having toīe read from disk. Tables each time in order to rule out any anomalies. SQL Profiler to capture the performance metrics for each statement.įor my test I ran these queries 10 times restoring a baseline backup of the original Some simple DML statements to see if there are any performance differences with Before we fix this let's first run through The above output confirms our assumption, the index on the GUID column does have ORDER BY indexstats.avg_fragmentation_in_percent DESC WHERE OBJECT_NAME(ind.OBJECT_ID) like '%testtable%' INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,įROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats Let's take a look at the fragmentation of each index I would suspect that since the GUID values are random there wouldīe quite a bit more fragmentation with this datatype versus the integer which isĮver increasing.

Or if there is there some fragmentation that is contributing to the extra disk spaceīeing used. Space used is due to the fact that it's a 4 byte datatype vs 16 byte datatype WHERE OBJECT_NAME(i.) like '%testtable%'Īs would be expected the index on the GUID column is using quite a bit more space.īut one thing we should consider when looking at this is whether all this extra Take a look at just how much more disk space it uses. Index on the GUID column would be much larger. That an integer requires 4 bytes and a GUID uses 16 bytes I would expect that the Of the other index characteristics, disk space usage and fragmentation. SELECT we take a look at the performance let's first take a look at some INSERT INTO testtable_guid (col1, col2, col3, col4) INSERT INTO testtable_int (col1, col2, col3, col4) CREATE TABLE testtable_int ( NOT NULL primary key clustered,ĬREATE TABLE testtable_guid ( NOT NULL primary key clustered,

Mysql uuid vs auto increment performance