Re: Seeking advice on currency type
In article [EMAIL PROTECTED], Hassan Schroeder [EMAIL PROTECTED] writes: Paul DuBois wrote: Before MySQL 5, DECIMAL values are stored as strings, so operations are less efficient than for integers. In MySQL 5, the representation of DECIMAL has changed to binary format, so it's more efficient than before. It's probably safe to say that integer is still a bit more efficient, but the difference between integer and DECIMAL will be less than before. Are you referring to operations in the sense of comparing two values in a SQL statement? versus simply retrieving or inserting a value? I'd say storing and converting doesn't matter much, but currency amounts are often summed up (say for a monthly report over a huge table). For this INT should be a big win. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on currency type
At 16:35 -0700 4/29/05, Hassan Schroeder wrote: Paul DuBois wrote: Before MySQL 5, DECIMAL values are stored as strings, so operations are less efficient than for integers. In MySQL 5, the representation of DECIMAL has changed to binary format, so it's more efficient than before. It's probably safe to say that integer is still a bit more efficient, but the difference between integer and DECIMAL will be less than before. Are you referring to operations in the sense of comparing two values in a SQL statement? versus simply retrieving or inserting a value? I'm referring to all of it. For the new DECIMAL type, storage requirements are less than before, and comparisons are faster. Either way, do you have a feel (or access to any studies) for the impact of using DECIMAL instead of INTEGER for currency? I'd just wonder if it's enough to make up for all the required conversions going on in the app layer :-) I don't believe any formal benchmarks have been done yet. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Seeking advice on currency type
Hello, I am seeking to create a table to hold prices for products. I am not sure what type would be best. According to the docs, I have many choices, dec, double, float, etc. Can anyone give me an idea, of which is the proper choice for handling US currency like so: 1500.99 Thanks, Scott K Purcell | Developer | VERTIS | 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on currency type
Scott Purcell wrote: I am seeking to create a table to hold prices for products. I am not sure what type would be best. According to the docs, I have many choices, dec, double, float, etc. Can anyone give me an idea, of which is the proper choice for handling US currency like so: 1500.99 I can't imagine any reason to use a type other than DECIMAL for a currency value. YMMV! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on currency type
In article [EMAIL PROTECTED], Hassan Schroeder [EMAIL PROTECTED] writes: Scott Purcell wrote: I am seeking to create a table to hold prices for products. I am not sure what type would be best. According to the docs, I have many choices, dec, double, float, etc. Can anyone give me an idea, of which is the proper choice for handling US currency like so: 1500.99 I can't imagine any reason to use a type other than DECIMAL for a currency value. A reason could be performance. Storing cent values in an INT field is more efficient. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on currency type
Harald Fuchs wrote: I can't imagine any reason to use a type other than DECIMAL for a currency value. A reason could be performance. Storing cent values in an INT field is more efficient. Are you saying that storing and/or retrieving a DECIMAL value takes appreciably more time than an INTEGER? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on currency type
At 15:24 -0700 4/29/05, Hassan Schroeder wrote: Harald Fuchs wrote: I can't imagine any reason to use a type other than DECIMAL for a currency value. A reason could be performance. Storing cent values in an INT field is more efficient. Are you saying that storing and/or retrieving a DECIMAL value takes appreciably more time than an INTEGER? Before MySQL 5, DECIMAL values are stored as strings, so operations are less efficient than for integers. In MySQL 5, the representation of DECIMAL has changed to binary format, so it's more efficient than before. It's probably safe to say that integer is still a bit more efficient, but the difference between integer and DECIMAL will be less than before. Details: http://dev.mysql.com/doc/mysql/en/precision-math-decimal-changes.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seeking advice on currency type
Here are datatypes and sizes. The problem can be storage. http://dev.mysql.com/doc/mysql/en/storage-requirements.html Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: samedi 30 avril 2005 00:25 To: mysql@lists.mysql.com Subject: Re: Seeking advice on currency type Harald Fuchs wrote: I can't imagine any reason to use a type other than DECIMAL for a currency value. A reason could be performance. Storing cent values in an INT field is more efficient. Are you saying that storing and/or retrieving a DECIMAL value takes appreciably more time than an INTEGER? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on currency type
Paul DuBois wrote: Before MySQL 5, DECIMAL values are stored as strings, so operations are less efficient than for integers. In MySQL 5, the representation of DECIMAL has changed to binary format, so it's more efficient than before. It's probably safe to say that integer is still a bit more efficient, but the difference between integer and DECIMAL will be less than before. Are you referring to operations in the sense of comparing two values in a SQL statement? versus simply retrieving or inserting a value? Either way, do you have a feel (or access to any studies) for the impact of using DECIMAL instead of INTEGER for currency? I'd just wonder if it's enough to make up for all the required conversions going on in the app layer :-) -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
