… recently posted an article comparing UUID and auto_increment primary keys, basically advertising to use UUID instead of primary keys. I wanted to clarify… can be fixed in MySQL 5.1 Data Clustering This again applies to Innodb tables aspect of primary key selection – you often… of 40305 rows/sec. For UUID process took over 12 hours and is still going. From MySQL status I can see it…
Post: MySQL Partitioning - can save you or kill you
December 11, 2010 By 14 Comments
I wanted for a while to write about using MySQL Partitioning for Performance Optimization and I just got a relevant … KEY (`id`), KEY `uu` (`uu`), ) ENGINE=InnoDB The access pattern to this table is to lookup data by “uu” which has UUID values… to the leaf key to verify such value does not exist, which reduced performance for random selects by UUID from 400 to…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
…` int(10) unsigned NOT NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j… explain if someone curious, it did not really change beside key lengths: mysql> explain select sum(t1.i+t2.j+length(t2… test was – latin1 encoding is enough for most character based keys – uuid, sha1/md5 based etc. Latin1 encoding indeed gave significant improvement…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number … data, uses non-deterministic functions such as UUID(), RAND(), CONNECTION_ID() etc it will not be… simple queries with select done by primary key, obviously there is no upper boundary – Very …
Comment: To UUID or not to UUID ?
March 14, 2007 By 28 Comments
… out on a few key points. One. UUIDs are about as pointless as sequence generators for my goals. UUIDs don’t have to… data like LiveJournal, Google, Flickr, etc then you can route keys. Since hashcodes are determistic you can figure out that this… though is the key routing. It just makes it easy to build larger clustered databases. I just with MySQL had support for…
Comment: To UUID or not to UUID ?
March 14, 2007 By 28 Comments
Kevin, First the stuff MySQL calls UUID are GUID at least if you use Wikipedia definition: http://… point – this corresponds to simple swap of auto_increment to UUID without changing application design or anything which is in my… often bad idea. If you use SHA1 as “natural” unique keys based on object contents you can design application differently and…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
… get inserts in very random order, such as indexes on UUID column or something similar. It also produces a lot better…` char(64) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB AUTO_INCREMENT=12582913 DEFAULT CHARSET=latin1 mysql> select * from a order… set (3 hours 3 min 35.15 sec) mysql> alter table a drop key c; Query OK, 0 rows affected (0.46…
Post: Stored Function to generate Sequences
April 2, 2008 By 15 Comments
…, `val` int(10) unsigned NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert… more sequences, which also can be helpful: mysql> select seq(‘one’),seq(‘two’),seq(‘one’),seq(‘one’);… of sequences and caching them in applications, using UUID_SHORT() and other methods which do not…
Comment: Using CHAR keys for joins, how much is the overhead ?
October 23, 2008 By 18 Comments
[...] Using CHAR keys for joins, how much is the overhead ? | MySQL Performance Blog (tags: uuid mysql) [...]
