Comment lookup in infobright Data Warehouse

Comment lookup in infobright Data Warehouse

The infobright Data Warehouse can compress and store a large amount of data in High-strength compression. The process of constant query is to decompress the data, of course, the detailed introduction has been mentioned before. Here we will not introduce the process (). In infobright, we support all the original MySQL Data Types, the efficiency of integer calculation is higher than that of other types, which is similar to that of MySQL. The more efficient types in infobright are as follows:

1. TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
2. DECIMAL (DECIMAL precision should be minimized)
3. DATE, TIME

The three types of data can have a relatively high compression ratio and query speed, while the efficiency is relatively low, the data types that are not recommended are:

1. binary varbinary (BINARY type)
2. FLOAT
3. DOUBLE
4. VARCHAR
5. tinytext text (non-Unicode type with variable length)

The efficiency of these data types is relatively low and the compression ratio is not very high. The VARCHAR field is less efficient than the CHAR field in MySQL, of course, in some business scenarios, CHAR (VARCHAR) is often used for frequent queries, but the number of data records is not many (no more than 10000 rows, in addition, there are more than 10 types of data, similar to data such as province and UUID), you can use the comment lookup method to create the DDL during table creation to improve the query efficiency as follows:

# Original table creation DDL

Create table 'test _ default '(

'Dstphone' varchar (11) default null,

'Upload id' varchar (255) DEFAULT NULL

) ENGINE = brighthouse default charset = utf8;

# Comment lookup table creation DDL

Create table 'test _ lookup '(

'Dstphone' varchar (11) default null comment 'lookup ',

'Regionid' varchar (255) default null comment 'lookup'

) ENGINE = brighthouse default charset = utf8;

Note that the comment lookup method can only be used in CHAR (VARCHAR) at present, and the cost of higher query efficiency is the disk overhead, because infobright is decompression during normal queries, you can use comment lookup to reduce the compression ratio and quickly decompress data during queries, the following shows that the comment lookup method is different from the default compression ratio when creating a table.

The query efficiency is as follows:

You can check the disk space occupied by the same data, but the query records cannot exceed 10000 rows. Otherwise, the efficiency will be reduced:

Therefore, you need to select one based on your actual needs.