Rick Faircloth wrote: > I'm thinking about changing from using auto-incrementing integers > to CF-generated UUID's for primary keys in my mysql db's. > > Any drawbacks in doing that?
1. Storage size. Let's suppose you have a table for a many-to-many relation. That is 72 bytes for the 2 fields and another 72 bytes for the indexes. With integers that would have been 16 bytes. This directly translates to query performance because of the extra I/O the database has to do. You may or may not care about that since the overhead is a constant factor, but even at 36 vs. 16 bytes (string vs. binary UUID representation) that performance difference is measurable: http://jochem.vandieten.net/2008/02/06/postgresql-uuids-and-coldfusion-1/ 2. String vs. integer comparison and charsets Maybe not relevant for MySQL, but since you are shifting from from an integer to a string you should take the effects of character set conversion into consideration. Implicit character set conversion can degrade a query from an index scan to a table scan: http://jochem.vandieten.net/2008/03/22/ms-sql-server-and-the-coldfusion-string-format-setting/ 3. Generation time The speed of UUID generation in CF is limited to about 0.64 / clockresolution. So on Windows where the Java clock resolution is 10 milliseconds, that translates to 64 UUIDs per second. In some processes that is a bottleneck. (It can go faster, but then you get the problem where time starts moving too fast.) Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301927 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
