Brian recently posted an article comparing UUID and auto_increment primary keys, basically advertising to use UUID instead of primary keys. I wanted to clarify this… tables containing just integer auto_increment primary key and containing char(36) value and used for UUID primary key and when I populated it with…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… unlike other benchmarks I decided to do Join not on primary key and have query to read data for both tables. If…: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 262144 Extra: *************************** 2. row… test was – latin1 encoding is enough for most character based keys – uuid, sha1/md5 based etc. Latin1 encoding indeed gave significant improvement…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… unusual setup. Q: Table1 has a primary key. Table2 joins to table1 using Table1′s primary key. Should table2 have an index on the… cases should auto-increment be used as primary key? A: Auto-increment is a good default primary key. You should pick something else if… auto increment. Having said that there are many people using UUID rather successful in applications which do not need to be…
Comment: To UUID or not to UUID ?
December 15, 2007 By 28 Comments
… the auto_increment primary key and then using a persons First, Middle and Last name to search for the UUID implementation. Write a… hyphenation if you need to display it. However, the primary use for UUIDs or GUIDs is data portability, not speed for searching… data stored in many locations you have to manage this primary key much closer and have the ability to generate something that…
Comment: To UUID or not to UUID ?
June 8, 2010 By 28 Comments
… insert performance, primary keys should be inserted with least changing bits first, correct? I wrote two functions to encode and decode UUIDs, please…(uuid),5,2)),’-', HEX(MID(REVERSE(uuid),7,2)),’-', HEX(MID(REVERSE(uuid),9,2)),’-', HEX(RIGHT(REVERSE(uuid),6)) ); END $$ SELECT DECODE_UUID(ENCODE_UUID…
Post: MySQL Partitioning - can save you or kill you
December 11, 2010 By 45 Comments
…, PRIMARY KEY (`id`), KEY `uu` (`uu`), ) ENGINE=InnoDB The access pattern to this table is to lookup data by “uu” which has UUID values… mainly due to modifying the uu index. This is because UUID() spreads values prefix very well effectively giving almost uniform access… to the leaf key to verify such value does not exist, which reduced performance for random selects by UUID from 400 to…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… get data physically laid out in primary key order as well as get better feel for primary key and index pages and so using… get inserts in very random order, such as indexes on UUID column or something similar. It also produces a lot better…) unsigned NOT NULL AUTO_INCREMENT, `c` char(64) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB AUTO_INCREMENT=12582913 DEFAULT CHARSET…
Comment: To UUID or not to UUID ?
March 31, 2009 By 28 Comments
… pseudo-random) values. A reason you would want to use UUID/GUID over auto incrementing integers is when you’re synchronizing… by incremental integers for primary keys you’re constantly updating IDs on record inserts during synchronization. If the UUID() values were random (like… chance of collisions. I would also like to see a UUID data type (which would use 16-byte binary representation instead…
Post: Stored Function to generate Sequences
April 2, 2008 By 17 Comments
…` varchar(20) NOT NULL, `val` int(10) unsigned NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into seq values… allocating “ranges” of sequences and caching them in applications, using UUID_SHORT() and other methods which do not require global lock…
Comment: To UUID or not to UUID ?
March 26, 2007 By 28 Comments
… though? Does this happen automatically? If I replaced my primary keys with UUID() generated varchars can I just use a normal UPDATE statement…
