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) | 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.10. UPDATE Syntax http://dev.mysql.com/doc/refman/5.1/en/update.html says, "UPDATE ... table_reference ..." (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.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.
|
|