How can I optimize UUID storage in MySQL and improve query performance?

Optimizing UUID Storage in MySQL: Converting to a Number

While UUIDs offer uniqueness, their storage as strings in MySQL can hinder performance. To enhance data retrieval efficiency, it's advisable to store UUIDs as numbers instead.

Conversion Process

1. Remove Dashes:
   Eliminate the dashes from the UUID, e.g., "110E8400-E29B-11D4-A716-446655440000" becomes "110E8400E29B11D4A716446655440000".
2. BINARY(16) Field:
   MySQL stores UUIDs efficiently in BINARY fields. Since UUIDs have 128 bits, BINARY(16) is ideal, accommodating the full UUID size.

3. Insert Using UNHEX():
   Insert the UUID into the BINARY field using the UNHEX() function, e.g.:

   INSERT INTO Table (FieldBin) VALUES (UNHEX("110E8400E29B11D4A716446655440000"))

4. Query Using HEX():
   To retrieve the UUID, use the HEX() function, e.g.:

   SELECT HEX(FieldBin) AS FieldBin FROM Table

5. Re-add Dashes:
   In your Ruby code, re-insert the dashes at the appropriate positions to match the original UUID format.

Example

require 'mysql2'

client = Mysql2::Client.new(
  host: 'localhost',
  username: 'root',
  password: 'password',
  database: 'test_database'
)

uuid_binary = client.query(
  'SELECT HEX(FieldBin) AS FieldBin FROM test_table'
).first['FieldBin']

uuid = uuid_binary.insert(9, '-').insert(14, '-').insert(19, '-').insert(24, '-')

puts uuid

By storing UUIDs as numbers, you can optimize data retrieval in MySQL, significantly improving query performance.

The above is the detailed content of How can I optimize UUID storage in MySQL and improve query performance?. For more information, please follow other related articles on the PHP Chinese website!