Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If I understand correctly, you're using UUIDs in your primary column? People will say that a regular (integer) primary key will be faster , but there's another way using MySQL's dark side. In fact, MySQL is faster using binary than anything else when indexes are required.</p> <p>Since UUID is 128 bits and is written as hexadecimal, it's very easy to speed up and store the UUID.</p> <p>First, in your programming language remove the dashes</p> <p>From <code>110E8400-E29B-11D4-A716-446655440000</code> to <code>110E8400E29B11D4A716446655440000</code>.</p> <p>Now it's 32 chars (like an MD5 hash, which this also works with).</p> <p>Since a single <code>BINARY</code> in MySQL is 8 bits in size, <code>BINARY(16)</code> is the size of a UUID (8*16 = 128).</p> <p>You can insert using:</p> <p><code>INSERT INTO Table (FieldBin) VALUES (UNHEX("110E8400E29B11D4A716446655440000"))</code></p> <p>and query using:</p> <p><code>SELECT HEX(FieldBin) AS FieldBin FROM Table</code></p> <p>Now in your programming language, re-insert the dashes at the positions 9, 14, 19 and 24 to match your original UUID. If the positions are always different you could store that info in a second field.</p> <p>Full example :</p> <pre><code>CREATE TABLE `test_table` ( `field_binary` BINARY( 16 ) NULL , PRIMARY KEY ( `field_binary` ) ) ENGINE = INNODB ; INSERT INTO `test_table` ( `field_binary` ) VALUES ( UNHEX( '110E8400E29B11D4A716446655440000' ) ); SELECT HEX(field_binary) AS field_binary FROM `test_table` </code></pre> <p>If you want to use this technique with any hex string, always do <code>length / 2</code> for the field length. So for a sha512, the field would be <code>BINARY (64)</code> since a sha512 encoding is 128 characters long.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to store uuid as number?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USNateS
    1. USDavid Bélanger
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COuse the unhex function make the result is human unreadable.
      singulars
      1. PO
      1. USChamnap
    2. CO@Chamnap UNHEX function will convert HEX to BINARY in your database. You can then use indexes on it without a problem and with a gain of performance (yes yes !). You then read the data with the function `HEX` like in my example. So no, you can't read the result from `UNHEX`, but you can if use `HEX`. Remember the computer is made of binary, always faster.
      singulars
      1. PO
      1. USDavid Bélanger
    3. CO@Chamnap Let's say you have 10 000 rows in your database and they have been added using UNHEX function and you want to search for the UUID `110E8400-E29B-11D4-A716-446655440000`. Just do something like : `SELECT * FROM test_table WHERE field_binary LIKE CONCAT("%", UNHEX('110E8400E29B11D4A716446655440000'), "%")`
      singulars
      1. PO
      1. USDavid Bélanger
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload