I've been using UUIDs in my systems for a while now for a variety of reasons ranging from logging to delayed correlation. The formats I used changed as I became less naive from:

  1. VARCHAR(255)
  2. VARCHAR(36)
  3. CHAR(36)
  4. BINARY(16)

It was when I reached the final one BINARY(16) that I started to compare performance with basic auto-increment integer. The test and results are shown below, but if you just want the summary, it indicates that INT AUTOINCREMENT and BINARY(16) RANDOM have identical performance on data ranges up to 200,000 (the database was pre-populated prior to tests).

I was initially sceptical towards using UUIDs as primary keys, and indeed I still am, however I see potential here to create a flexible database that can use both. Whereas many people stress over the advantages of either, what are the disadvantages cancelled out by using both data types?

  • PRIMARY INT
  • UNIQUE BINARY(16)

The use case for this type of set up would be the traditional primary key for inter-table relationships, with unique identifier used for inter-system relationships.

What I am essentially trying to discover is difference in efficiency between the two approaches. Besides the quadruple disk space used, which may be largely negligible after additional data is added, they appear to me to be the same.

Schema:

-- phpMyAdmin SQL Dump
-- version 4.0.10deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 22, 2015 at 10:54 AM
-- Server version: 5.5.44-0ubuntu0.14.04.1
-- PHP Version: 5.5.29-1+deb.sury.org~trusty+3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `with_2id`
--

CREATE TABLE `with_2id` (
  `guidl` bigint(20) NOT NULL,
  `guidr` bigint(20) NOT NULL,
  `data` varchar(255) NOT NULL,
  PRIMARY KEY (`guidl`,`guidr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `with_guid`
--

CREATE TABLE `with_guid` (
  `guid` binary(16) NOT NULL,
  `data` varchar(255) NOT NULL,
  PRIMARY KEY (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `with_id`
--

CREATE TABLE `with_id` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=197687 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Insert benchmark:

function benchmark_insert(PDO $pdo, $runs)
{
    $data = 'Sample Data';

    $insert1 = $pdo->prepare("INSERT INTO with_id (data) VALUES (:data)");
    $insert1->bindParam(':data', $data);

    $insert2 = $pdo->prepare("INSERT INTO with_guid (guid, data) VALUES (:guid, :data)");
    $insert2->bindParam(':guid', $guid);
    $insert2->bindParam(':data', $data);

    $insert3 = $pdo->prepare("INSERT INTO with_2id (guidl, guidr, data) VALUES (:guidl, :guidr, :data)");
    $insert3->bindParam(':guidl', $guidl);
    $insert3->bindParam(':guidr', $guidr);
    $insert3->bindParam(':data',  $data);

    $benchmark = array();

    $time = time();
    for ($i = 0; $i < $runs; $i++) {
        $insert1->execute();
    }
    $benchmark[1] = 'INC ID:     ' . (time() - $time);

    $time = time();
    for ($i = 0; $i < $runs; $i++) {
        $guid  = openssl_random_pseudo_bytes(16);

        $insert2->execute();
    }
    $benchmark[2] = 'GUID:       ' . (time() - $time);

    $time = time();
    for ($i = 0; $i < $runs; $i++) {
        $guid  = openssl_random_pseudo_bytes(16);
        $guidl = unpack('q', substr($guid, 0, 8))[1];
        $guidr = unpack('q', substr($guid, 8, 8))[1];

        $insert3->execute();
    }
    $benchmark[3] = 'SPLIT GUID: ' . (time() - $time);

    echo 'INSERTION' . PHP_EOL;
    echo '=============================' . PHP_EOL;
    echo $benchmark[1] . PHP_EOL;
    echo $benchmark[2] . PHP_EOL;
    echo $benchmark[3] . PHP_EOL . PHP_EOL;
}

Select benchmark:

function benchmark_select(PDO $pdo, $runs) {
    $select1 = $pdo->prepare("SELECT * FROM with_id WHERE id = :id");
    $select1->bindParam(':id', $id);

    $select2 = $pdo->prepare("SELECT * FROM with_guid WHERE guid = :guid");
    $select2->bindParam(':guid', $guid);

    $select3 = $pdo->prepare("SELECT * FROM with_2id WHERE guidl = :guidl AND guidr = :guidr");
    $select3->bindParam(':guidl', $guidl);
    $select3->bindParam(':guidr', $guidr);

    $keys = array();

    for ($i = 0; $i < $runs; $i++) {
        $kguid  = openssl_random_pseudo_bytes(16);
        $kguidl = unpack('q', substr($kguid, 0, 8))[1];
        $kguidr = unpack('q', substr($kguid, 8, 8))[1];
        $kid = mt_rand(0, $runs);

        $keys[] = array(
            'guid'  => $kguid,
            'guidl' => $kguidl,
            'guidr' => $kguidr,
            'id'    => $kid
        );
    }

    $benchmark = array();

    $time = time();
    foreach ($keys as $key) {
        $id = $key['id'];
        $select1->execute();
        $row = $select1->fetch(PDO::FETCH_ASSOC);
    }
    $benchmark[1] = 'INC ID:     ' . (time() - $time);


    $time = time();
    foreach ($keys as $key) {
        $guid = $key['guid'];
        $select2->execute();
        $row = $select2->fetch(PDO::FETCH_ASSOC);
    }
    $benchmark[2] = 'GUID:       ' . (time() - $time);

    $time = time();
    foreach ($keys as $key) {
        $guidl = $key['guidl'];
        $guidr = $key['guidr'];
        $select3->execute();
        $row = $select3->fetch(PDO::FETCH_ASSOC);
    }
    $benchmark[3] = 'SPLIT GUID: ' . (time() - $time);

    echo 'SELECTION' . PHP_EOL;
    echo '=============================' . PHP_EOL;
    echo $benchmark[1] . PHP_EOL;
    echo $benchmark[2] . PHP_EOL;
    echo $benchmark[3] . PHP_EOL . PHP_EOL;
}

Tests:

$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');

benchmark_insert($pdo, 1000);
benchmark_select($pdo, 100000);

Results:

INSERTION
=============================
INC ID:     3
GUID:       2
SPLIT GUID: 3

SELECTION
=============================
INC ID:     5
GUID:       5
SPLIT GUID: 6
up vote 9 down vote accepted

UUIDs are a performance disaster for very large tables. (200K rows is not "very large".)

Your #3 is really bad when the CHARCTER SET is utf8 -- CHAR(36) occupies 108 bytes! Update: There are ROW_FORMATs for which this will stay 36.

UUIDs (GUIDs) are very "random". Using them as either a UNIQUE or a PRIMARY key on large tables is very inefficient. This is because of having to jump around the table/index each time you INSERT a new UUID or SELECT by UUID. When the table/index is too large to fit in cache (see innodb_buffer_pool_size, which must be smaller than RAM, typically 70%), the 'next' UUID may not be cached, hence a slow disk hit. When the table/index is 20 times as big as the cache, only 1/20th (5%) of hits are cached -- you are I/O-bound. Generalization: The inefficiency applies to any "random" access -- UUID / MD5 / RAND() / etc

So, don't use UUIDs unless either

  • you have "small" tables, or
  • you really need them because of generating unique ids from different places (and have not figured out another way to do it).

More on UUIDs: http://mysql.rjweb.org/doc.php/uuid (It includes functions for converting between standard 36-char UUIDs and BINARY(16).) Update: MySQL 8.0 has a builtin function for such.

Having both a UNIQUE AUTO_INCREMENT and a UNIQUE UUID in the same table is a waste.

  • When an INSERT occurs, all unique/primary keys must be checked for duplicates.
  • Either unique key is sufficient for InnoDB's requirement of having a PRIMARY KEY.
  • BINARY(16) (16 bytes) is somewhat bulky (an argument against making it the PK), but not that bad.
  • The bulkiness matters when you have secondary keys. InnoDB silently tacks the PK onto the end of each secondary key. The main lesson here is to minimize the number of secondary keys, especially for very large tables. Elaboration: For one secondary key, the bulkiness debate usually ends in a draw. For 2 or more secondary keys, a fatter PK usually leads to a bigger disk footprint for the table including its indexes.

For comparision: INT UNSIGNED is 4 bytes with range of 0..4 billion. BIGINT is 8 bytes.

Italics Updates/etc were added Sep, 2017; nothing critical changed.

  • Thank you for your answer, I was less aware of the loss of cache optimization. I was less worried about bulky foreign keys but I see how it would eventually become an issue. I am reluctant however to remove their use entirely however as they do prove very useful for cross system interaction. BINARY(16) I think we both agree is the most efficient way to store a UUID, but regarding the UNIQUE index, should I simply use a regular index? The bytes are generated using cryptographically secure RNGs, so shall I depend on the randomness entirely, and forgo the checks? – Flosculus Oct 27 '15 at 9:29
  • A non-unique index would help performance some, but even a regular index needs to be updated eventually. What is your projected table size? Will it eventually be too large to cache? A suggested value for innodb_buffer_pool_size is 70% of available ram. – Rick James Oct 30 '15 at 14:03
  • Its database 1.2 GB after 2 months, largest table is 300MB, but the data will never disappear, so however long it will last, 10 years maybe. Granted less than half of the tables will even need UUIDs, so I will remove them from the most superficial use cases. Which leaves the one that will need them currently at 50,000 rows and 250MB, or 30 - 100 GB in 10 years. – Flosculus Oct 30 '15 at 15:12
  • 2
    In 10 years, you won't be able to buy a machine with only 100GB of RAM. You'll always fit in RAM, so my comments probably won't apply to your case. – Rick James Oct 30 '15 at 15:22
  • 1
    @a_horse_with_no_name - In older versions, it was always 3x. Only newer versions got smart about that. Perhaps that was 5.1.24; that's probably old enough for me to forget about it. – Rick James Jan 2 '17 at 21:29

'Rick James' said in accepted answer: "Having both a UNIQUE AUTO_INCREMENT and a UNIQUE UUID in the same table is a waste". But this test (I did it on my machine) show different facts.

For example: with the test (T2) I make table with (INT AUTOINCREMENT) PRIMARY and UNIQUE BINARY(16) and another field as title, then I insert more than 1.6M rows with very good performance, but with another testing (T3) I did the same but the result is slow after inserting 300,000 rows only.

This is my testing result:

T1:
char(32) UNIQUE with auto increment int_id
after: 1,600,000
10 sec for inserting 1000 rows
select + (4.0)
size:500mb

T2:
binary(16) UNIQUE with auto increment int_id
after: 1,600,000
1 sec for inserting 1000 rows
select +++ (0.4)
size:350mb

T3:
binary(16) UNIQUE without auto increment int_id
after: 350,000
5 sec for inserting 1000 rows
select ++ (0.3)
size:118mb (~ for 1,600,000 will be 530mb)

T4:
auto increment int_id without binary(16) UNIQUE
++++

T5:
uuid_short() int_id without binary(16) UNIQUE
+++++*

So binary(16) UNIQUE with auto increment int_id is better than binary(16) UNIQUE without auto increment int_id.

Update:

I make the same test again and record more details. this is full code and results comparison between (T2) and (T3) as explained above.

(T2) create tbl2 (mysql):

CREATE TABLE test.tbl2 (
  int_id INT(11) NOT NULL AUTO_INCREMENT,
  rec_id BINARY(16) NOT NULL,
  src_id BINARY(16) DEFAULT NULL,
  rec_title VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (int_id),
  INDEX IDX_tbl1_src_id (src_id),
  UNIQUE INDEX rec_id (rec_id)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

(T3) create tbl3 (mysql):

CREATE TABLE test.tbl3 (
  rec_id BINARY(16) NOT NULL,
  src_id BINARY(16) DEFAULT NULL,
  rec_title VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (rec_id),
  INDEX IDX_tbl1_src_id (src_id)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

This is full testing code, it is inserting 600,000 records into tbl2 or tbl3 (vb.net code):

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim res As String = ""
        Dim i As Integer = 0
        Dim ii As Integer = 0
        Dim iii As Integer = 0

        Using cn As New SqlClient.SqlConnection
            cn.ConnectionString = "Data Source=.\sql2008;Integrated Security=True;User Instance=False;MultipleActiveResultSets=True;Initial Catalog=sourcedb;"
            cn.Open()
            Using cmd As New SqlClient.SqlCommand
                cmd.Connection = cn
                cmd.CommandTimeout = 0
                cmd.CommandText = "select recID, srcID, rectitle from textstbl order by ID ASC"

                Using dr As SqlClient.SqlDataReader = cmd.ExecuteReader

                    Using mysqlcn As New MySql.Data.MySqlClient.MySqlConnection
                        mysqlcn.ConnectionString = "User Id=root;Host=localhost;Character Set=utf8;Pwd=1111;Database=test"
                        mysqlcn.Open()

                        Using MyCommand As New MySql.Data.MySqlClient.MySqlCommand
                            MyCommand.Connection = mysqlcn

                            MyCommand.CommandText = "insert into tbl3 (rec_id, src_id, rec_title) values (UNHEX(@rec_id), UNHEX(@src_id), @rec_title);"
                            Dim MParm1(2) As MySql.Data.MySqlClient.MySqlParameter
                            MParm1(0) = New MySql.Data.MySqlClient.MySqlParameter("@rec_id", MySql.Data.MySqlClient.MySqlDbType.String)
                            MParm1(1) = New MySql.Data.MySqlClient.MySqlParameter("@src_id", MySql.Data.MySqlClient.MySqlDbType.String)
                            MParm1(2) = New MySql.Data.MySqlClient.MySqlParameter("@rec_title", MySql.Data.MySqlClient.MySqlDbType.VarChar)

                            MyCommand.Parameters.AddRange(MParm1)
                            MyCommand.CommandTimeout = 0

                            Dim mytransaction As MySql.Data.MySqlClient.MySqlTransaction = mysqlcn.BeginTransaction()
                            MyCommand.Transaction = mytransaction

                            Dim sw As New Stopwatch
                            sw.Start()

                            While dr.Read
                                MParm1(0).Value = dr.GetValue(0).ToString.Replace("-", "")
                                MParm1(1).Value = EmptyStringToNullValue(dr.GetValue(1).ToString.Replace("-", ""))
                                MParm1(2).Value = gettitle(dr.GetValue(2).ToString)

                                MyCommand.ExecuteNonQuery()

                                i += 1
                                ii += 1
                                iii += 1

                                If i >= 1000 Then
                                    i = 0

                                    Dim ts As TimeSpan = sw.Elapsed
                                    Me.Text = ii.ToString & " / " & ts.TotalSeconds

                                    Select Case ii
                                        Case 10000, 50000, 100000, 200000, 300000, 400000, 500000, 600000, 700000, 800000, 900000, 1000000
                                            res &= "On " & FormatNumber(ii, 0) & ": last inserting 1000 records take: " & ts.TotalSeconds.ToString & " second." & vbCrLf
                                    End Select

                                    If ii >= 600000 Then GoTo 100
                                    sw.Restart()
                                End If
                                If iii >= 5000 Then
                                    iii = 0

                                    mytransaction.Commit()
                                    mytransaction = mysqlcn.BeginTransaction()

                                    sw.Restart()
                                End If
                            End While
100:
                            mytransaction.Commit()

                        End Using
                    End Using
                End Using
            End Using
        End Using

        TextBox1.Text = res
        MsgBox("Ok!")
    End Sub

    Public Function EmptyStringToNullValue(MyValue As Object) As Object
        'On Error Resume Next
        If MyValue Is Nothing Then Return DBNull.Value
        If String.IsNullOrEmpty(MyValue.ToString.Trim) Then
            Return DBNull.Value
        Else
            Return MyValue
        End If
    End Function

    Private Function gettitle(p1 As String) As String
        If p1.Length > 255 Then
            Return p1.Substring(0, 255)
        Else
            Return p1
        End If
    End Function

End Class

The Result for (T2):

On 10,000: last inserting 1000 records take: 0.13709 second.
On 50,000: last inserting 1000 records take: 0.1772109 second.
On 100,000: last inserting 1000 records take: 0.1291394 second.
On 200,000: last inserting 1000 records take: 0.5793488 second.
On 300,000: last inserting 1000 records take: 0.1296427 second.
On 400,000: last inserting 1000 records take: 0.6938583 second.
On 500,000: last inserting 1000 records take: 0.2317799 second.
On 600,000: last inserting 1000 records take: 0.1271072 second.

~3 Minutes ONLY! to insert 600,000 records.
table size: 128 mb.

The Result for (T3):

On 10,000: last inserting 1000 records take: 0.1669595 second.
On 50,000: last inserting 1000 records take: 0.4198369 second.
On 100,000: last inserting 1000 records take: 0.1318155 second.
On 200,000: last inserting 1000 records take: 0.1979358 second.
On 300,000: last inserting 1000 records take: 1.5127482 second.
On 400,000: last inserting 1000 records take: 7.2757161 second.
On 500,000: last inserting 1000 records take: 14.3960671 second.
On 600,000: last inserting 1000 records take: 14.9412401 second.

~40 Minutes! to insert 600,000 records.
table size: 164 mb.
  • 2
    Please explain how your answer is more than just running their benchmark on your personal machine. Ideally an answer would discuss some of the trade offs involved instead of just benchmark outputs. – Erik Dec 27 '16 at 0:11
  • 1
    Some clarifications, please. What was innodb_buffer_pool_size? Where did "table size" come from? – Rick James Jan 2 '17 at 23:37
  • 1
    Please rerun, using 1000 for transaction size -- this may eliminate the strange hiccups in both tbl2 and tbl3. Also, print out the timing after the COMMIT, not before. This may eliminate some other anomalies. – Rick James Jan 2 '17 at 23:39
  • 1
    I'm not familiar with the language you are using, but I do see how different values of @rec_id and @src_id are being generated and applied to each row. Printing a couple of INSERT statements might satisfy me. – Rick James Jan 2 '17 at 23:53
  • 1
    Also, keep going past 600K. At some point (partially dependent on how big rec_title is), t2 will also fall of a cliff. It may even go slower than t3; I am not sure. Your benchmark is in a "doughnut hole" where t3 is temporarily slower. – Rick James Sep 15 '17 at 4:53

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.