Infobright

Integer data types are best in Infobright.

Type	Bytes	Maximum Value	Maximum Value Truncated in Words	Full Digits
TinyInt	1	127	1 hundred	2
SmallInt	2	32,767	32 thousand (kilo)	4
MediumInt	3	8,388,607	8 million (mega)	6
Integer	4	2,147,483,647	2 billion (giga)	9
BigInt	8	9,223,372,036,854,775,806	9 quintillion (exa)	18

Decimal (n, 0) is next best data type in Infobright.

Date and Time data types are also efficient in Infobright.

Char and VarChar are the most efficient string data types in Infobright.

Type	Bytes	Maximum Length
Char (n)	n	255
VarChar (n)	n + 2	65,532

Decimal (n ,d) is much more efficient than Float or Double in Infobright.

Avoid using Binary, Double, Float, Text, TinyText, and VarBinary data types in Infobright, when possible.

To optimize query performance in Infobright:

Avoid using OR in queries, and, if possible, use IN instead.
Avoid using functions or type cast operators on variables in queries.
Avoid including MyISAM tables in queries.
Avoid performing comparisons or arithmetical operations on two different data types (such as numbers and strings) in queries.
Avoid using NOT BETWEEN in JOIN conditions.
Use sub-SELECT instead of JOIN, if possible.
Use WHERE clauses to restrict selections to maximize the use of the Knowledge Grid.
Use COMMENT 'lookup' for CHAR and VARCHAR columns that have a greater than 10:1 ratio of total to distinct values and are often used in queries.
For each string column for which values are often queried in their entirety, add a checksum column, if it can be included in many appropriate queries. For example, for column VIN, which contains vehicle identification numbers, add a column VIN_Checksum equal to CONV (SUBSTR (MD5 (VIN), 1, 14), 16, 10).

Differences Between MySQL Documentation and Infobright Community Edition (ICE)

MySQL Documentation

Infobright Community Edition

10.2. Numeric Types - approximate

http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html says, "MySQL supports ... the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION)." (5/18/11)

http://www.infobright.org/wiki/Efficient_Data_Types/ says:

"DECIMAL — note: storing as(10,0)will be much faster than (10,5)....

DECIMAL is much more efficient than FLOAT or DOUBLE and therefore, should be used in place of FLOAT or DOUBLE if possible." (5/18/11)

10.2. Numeric Types - DECIMAL

http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html says, "The maximum number of digits forDECIMAL is 65." (4/21/11)

The maximum number of digits for DECIMAL is 18.

http://www.infobright.org/forums/viewthread/240/ says:

"INFOBRIGHT supports DEC/DECIMAL(18,x) as described below:

DEC(M, D)/(DECIMAL(M, D)) where 0 < M <= 18 and 0 <= D <=M" (12/7/08)

(Still the case on page 27 of the Infobright Community Edition 4.0.3 User Guide, file 2011_06_27_ICE_User_Guide.pdf, 6/27/11)

10.4.2. The BINARY and VARBINARY Types

http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html says, "The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that ... they contain byte strings rather than character strings."

Avoid using BINARY and VARBINARY, if possible.

http://www.infobright.org/wiki/Efficient_Data_Types/ says:

"Less optimized data types:

BINARY, VARBINARY" (5/18/11)

10.4.3. The BLOB ... Types

http://dev.mysql.com/doc/refman/5.1/en/blob.html says, "The four BLOB types are TINYBLOB, BLOB,MEDIUMBLOB, and LONGBLOB."

TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB cannot be used.

http://www.infobright.org/wiki/Supported_Data_Types_and_Values/ does not list any BLOB types as supported data types.

10.4.3. The ... TEXT Types

http://dev.mysql.com/doc/refman/5.1/en/blob.html says, "The four TEXT types are TINYTEXT, TEXT,MEDIUMTEXT, and LONGTEXT."

MEDIUMTEXT and LONGTEXT cannot be used.

Avoid using TINYTEXT and TEXT.

http://www.infobright.org/wiki/Supported_Data_Types_and_Values/ does not list MEDIUMTEXT or LONGTEXT as supported data types.

http://www.infobright.org/wiki/Efficient_Data_Types/ says:

"Less optimized data types: ...

TINYTEXT, TEXT"

12.1.17. CREATE TABLE Syntax - TEMPORARY

http://dev.mysql.com/doc/refman/5.1/en/create-table.html says:

"CREATE [TEMPORARY] TABLE" (1/27/12

At http://www.infobright.org/forums/viewthread/2729/ Jeff Kibler wrote, "create temp table is not on our roadmap for ICE." (1/25/12)

12.1.17. CREATE TABLE Syntax - CHAR and VARCHAR

http://dev.mysql.com/doc/refman/5.1/en/create-table.html says:

"data_type:

...

| CHAR[(length)]

[CHARACTER SET charset_name] [COLLATEcollation_name]

| VARCHAR(length)

[CHARACTER SET charset_name] [COLLATEcollation_name]" (8/14/11)

Infobright Community Edition 4.0.3 User Guide, file 2011_06_27_ICE_User_Guide.pdf, says, starting on page 29:

"Lookup Columns

Infobright provides an additional modifier for string data type columns, called a lookup column. The lookup column utilizes an integer substitution for values. You can declare a lookup column on a CHAR orVARCHAR column to increase its compression and performance in queries. However, to use a lookup column, the CHAR or VARCHARcolumn must meet the following criteria:

• There is no fixed upper limit for unique values in the column (cardinality). The total size of a dictionary, being the total length of all distinct values, will be loaded into RAM (for example: 1 million distinct values that are each 100-character wide will permanently occupy 100 MB of RAM.) ...

• The column must contain a large number of duplicate values: the ratio of total number of records to distinct values should be greater than 10. ...

To declare a column as a lookup column, add the comment‘lookup’ on the column. Enter the following command:

mysql> create table …

(… <<column name>> <<column type>> … comment ‘lookup’ … …)

engine=brighthouse;" (6/27/11)

At http://www.infobright.org/Forums/viewthread/2502/#8670, Jakub Wroblewski wrote, referrring to the number of unique values in a column suitable for comment ‘lookup',"50,000 is still not much, unless they are varchar(1000) or wider." (8/16/11)

12.1.17.1. CREATE TABLE ... SELECT Syntax

http://dev.mysql.com/doc/refman/5.1/en/create-table-)select.html says:

"You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;" (4/29/11)

ICE (infobright-3.5.2-x86_64) does not support the SELECT * FROM orig_tbl option of the CREATE TABLE statement for Brighthouse engine tables, as demonstrated by:

mysql> Create table t

-> select *

-> from b ;

ERROR 1031 (HY000): Table storage engine for 't' doesn't have this option (4/29/11)

12.2.2. DELETE Syntax

http://dev.mysql.com/doc/refman/5.1/en/delete.html says, "DELETE ... FROM tbl_name ..." (9/17/11)

Infobright Community Edition 4.0.3 User Guide, file 2011_06_27_ICE_User_Guide.pdf, page 36 says, "INSERT, UPDATE, and DELETE commands are not supported in Infobright [Community Edition] and should not be used to manipulate data in Infobright [Community Edition] tables." (6/27/11)

12.2.5. INSERT Syntax

http://dev.mysql.com/doc/refman/5.1/en/insert.html says, "INSERT ... [INTO] tbl_name ..." (9/17/11)

12.2.10. UPDATE Syntax

http://dev.mysql.com/doc/refman/5.1/en/update.html says, "UPDATE ... table_reference ..." (9/17/11)

12.4.5.6. SHOW COLUMNS Syntax

http://dev.mysql.com/doc/refman/5.1/en/show-columns.html says:

"SHOW [FULL] COLUMNS {FROM | IN} tbl_name[{FROM | IN} db_name]

[LIKE 'pattern' | WHERE expr]"

Infobright Community Edition 4.0.3 User Guide, file 2011_06_27_ICE_User_Guide.pdf, page 31 says, "Utilization of the FULL option will provide an estimate of the compression for each column." (6/27/11)

The date in parentheses after an entry is the date it was last verified.

Go to Top