Independent Digital

You can store anything in string types. But be aware that numbers are stored more efficiently using numeric columns. Numbers and strings are also treated differently when querying which will affect the result. You need to make the correct choice before creating the tables but it is possible to easily make changes after the tables have been created and the data loaded.

Use ALTER TABLE to change the type

Use PROCEDURE_ANALYSE( ) to get information about the tables columns including a suggested optimal type for that column.

• What kind of values will the column hold?

Think of what the values will be used for. Put numbers in numeric columns, strings in string columns, dates and times in temporal columns. For numbers, decide whether they are floating-point or integer type. What is the degree of accuracy required and what is the unit of measure? Use integers for whole measures and floating-point for fractionals. Bear in mind that the stored value does not necessarily have to be the display value. There are a number of MySQL functions that can be used to convert numbers.

When working with monetary numbers remember that float and double are subject to rounding off. For accuracy of storage and retrieval, use DECIMAL, which is represented as a string and is not rounded off. The problem is that any calculations done are done using floating-point operations where round off can occur. The answer is to represent all money as cents in an integer column.

• Do the values lie within a specific range?

The range of your values determines the types that you can use. Use the smallest type possible. This will reduce storage requirements and speed up processing. Use the biggest type if you don?t know the range. You can always use ALTER TABLE later to change the type. The range of values also determines the attributes that you can use with that data type. For example UNSIGNED for positive numbers. String values have a maximum length of 255 characters, which determines whether TINYTEXT, CHAR or a longer TEXT type should be used. Try using the SET or ENUM data types for strings of fixed values. They are more efficient and take up less storage space.

• What are the performance and efficiency issues?

Data types affect the query performance in a number of ways:

• Numeric operations are usually faster than string operations. So if possible, when working with strings, consider using SET and ENUM types which are processed as numbers.
• Smaller types are processed faster than larger types. They use less storage space and consequently reduce disk activity. For fixed-sized data types, use the smallest possible type.
• Fixed and variable length data types affect performance differently and varies depending on the storage engine. Fixed is preferable for MyISAM tables. It doesn?t matter with MEMORY tables, as values are stored internally as fixed-length. Similarly with InnoDB and BDB tables. Do tests using both and see whether there are any benefits in using either.
• NOT NULL columns are processed faster and use less storage space.

• The affects of Data Type choices

Consider the choice of a data type within the context of storage space, performance issues, allowable attributes and the required values. Your data type needs to slip comfortably within all these issues.