Comments - DECIMAL

7 years, 2 months ago James Cobban

Using a "packed" rather than multiple-precision binary representation for DECIMAL (and NUMERIC and FIXED) requires more storage and significantly reduces performance. All hardware platforms on which MariaDB operates have built-in multiple-precision binary arithmetic. Most current hardware actually accesses memory 128 bits at a time, in particular all processors that use ECC memory, which covers any hardware you would consider using to run a commercial server. A specific example of the difference in storage is that BIGINT(19) occupies 64 bits (8 bytes) while DECIMAL(19) occupies 72 bits (9 bytes) and requires three 32 bit additions to add two values in a subroutine, whereas BIGINT(19) requires one 64 bit addition in hardware. Arithmetic and search operations on DECIMAL(19) fields take at least twice as long as the same operations on BIGINT(19) fields. However DECIMAL(19) is a data type defined in the SQL standard, whereas BIGINT is an implementation extension. Beyond that support for 128 bit integers is required for, among other things, support of IPV6 addresses, which I am currently stuck representing as VARCHAR(40). Although the SQL standard is for some reason considered a proprietary document which means that I cannot find any official documentation on the web of exactly what the standard expects it seems that even defining a field as INTEGER(9), which merely provides a hint to tools which display the value of the field, is actually an extension beyond at least the SQL92 standard, otherwise I would like to be able to specify INTEGER(anything up to at least 60) and have the server decide what is the most efficient binary implementation to store the values in terms of performance, rather than requiring that I change the datatype name as the application requires a greater range of values. After all DECIMAL(p) changes the internal implementation as p increases, so why not INTEGER(p)?

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.