Data Types related to Infobright

Infobright supports all the original MySQL Data Types. The Integer type is more efficient than other data types. Use the following data types as much as possible:

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

DECIMAL to minimize the number of DECIMAL places)

DATE, TIME

Inefficient data types that are not recommended:

BINARY VARBINARY

FLOAT

DOUBLE

VARCHAR

TINYTEXT TEXT

Some experiences and notes about using Infobright data types:

1) the range of the Infobright value type is a little different from that of MySQL. For example, the minimum value of the Infobright Int type is-2147483647, and the minimum value of the MySQl Int type is-2147483648. Other numeric types have such problems.

2) use a small data type. For example, if SMALLINT is used, INT Is Not applicable. In this case, Infobright is consistent with MySQL.

3) avoid inefficient data types, such as TEXT and so on, and use DECIMAL instead of FLOAT as much as possible. However, after all, DECIMAL will lose precision.

4) Try to use VARCHAR as little as possible. Dynamic Varchar performance in MySQL is not strong, so avoid VARCHAR as much as possible. If applicable, you can choose to change VARCHAR to CHAR storage or INTEGER type. The advantage of VARCHAR is that the length of the allocated space is variable. Since Infobright has excellent compression performance, I think VARCHAR can be converted into CHAR. CHAR has better query and compression performance.

5) Use INT whenever possible. In many cases, you can convert some CHAR data to an integer. For example, the customer permanent id and customer id in the search log can be stored in BIGINT instead of CHAR. In fact, it is also a good choice to divide the time into three columns for storage: year, month, and day. In the system I can see, the time is basically the most frequently used field. It is obviously very important to improve the query performance of the time field. Of course, this should be based on the specific circumstances of the system, when doing data analysis, it is necessary to those time functions of MySQL.

6) The varchar and char fields can also use comment lookup, which can significantly improve the compression ratio and query performance.

Title: http://blog.chinaunix.net/u2/72637/showart_2306106.html