tag:blogger.com,1999:blog-15622618.post5511874784968590282..comments2008-07-16T17:27:40.664-07:00Devananda vdvhttp://www.blogger.com/profile/03378341125039609409noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-15622618.post-41271170847136427302008-07-16T17:27:00.000-07:002008-07-16T17:27:00.000-07:00Hi Devananda, <BR/><BR/>to tell you the truth I'd have to take a close look at the source myself to get to the bottom of it. My remark concerning the DECIMAL being internally represented as a string is from recollection of a remark by Jeremy Cole some time ago (think it was Heidelberg Developer conference, september 2007)Roland Boumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15622618.post-3118105208726451252008-07-12T13:24:00.000-07:002008-07-12T13:24:00.000-07:00This comment has been removed by a blog administrator.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15622618.post-6978806431663288392008-07-12T10:35:00.000-07:002008-07-12T10:35:00.000-07:00Roland,<BR/><BR/>Thanks for the encouragement :)<BR/><BR/>"I think it is because internally, DECIMAL uses a kind of string representation."<BR/><BR/>I thought that since MySQL 5.0.3, decimal has been stored in a binary format? Is it actually just a compressed string, and not in fact numeric? Guess I should dive into the source and find out for myself :)<BR/><BR/>Out of curiosity, I repeated the same tests joining uint:char(6) and char(6):char(6), to see how that stacks up to dec. I updated the original post with those results, but in short: <BR/>- char:char was 6% and 12% slower than uint:uint for the "SELECT value" and "SELECT avg(value)" tests, respectively,<BR/>- uint:char was 41% and 63% slower than uint:uint, respectively.<BR/><BR/>Considering that the char:char JOIN was almost as fast as uint:uint, whereas uint:char shows a very similar performance hit to all the tests involving decimal, I wonder if mysql is performing some sort of typecast even for dec:dec JOINs?Devananda vdvhttp://www.blogger.com/profile/03378341125039609409noreply@blogger.comtag:blogger.com,1999:blog-15622618.post-76120736785712612442008-07-12T04:08:00.000-07:002008-07-12T04:08:00.000-07:00Hi Devananda,<BR/><BR/>good to see you posting again :)<BR/><BR/>"Even though decimal(13,4) is only 6 bytes, it is significantly slower to join two columns of this type than two bigint columns. I wonder why...."<BR/><BR/>I think it is because internally, DECIMAL uses a kind of string representation. My guess is that this makes comparisons by numerical value slow.<BR/><BR/>I think your results clearly show that even joining on identical DECIMAL types it a good deal slower than over integers.Roland Boumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.com