Every database has a limit on the maximum size that can be taken by a single row and for MySQL, that limit is 65,535 bytes. This does not mean that a record is limited in size to 64kb because the database may store most of the data in internal tables. For example, a Text field can store 10M of data, but only takes up 11 bytes in the row with a pointer to the internal table where the data is actually stored. For another example, a Choice field set to "supercalifragilisticexpialidocious" still takes up only four bytes in the viewable table that the user edits, because it is represented by an integer that is then converted to text using an internal table.
However, it is still possible to hit the 64kb limit with very complex tables if they are not carefully designed. When creating complex tables where the 64kb limit is a concern, you can take these steps to minimize the space used:
- Split different types of data into different tables.
- Only import the linked fields that are really needed. As noted in the table below, each linked field that you select to be displayed takes up space.
Unless the field needs to be indexed, use Text fields in place of Short Text fields. Note however, that Text fields cannot be set as unique. Microsoft SQL Server also has a limitation that prevents Text fields from being indexed for rapid searching.
Note The data in Text fields is not stored in the table itself; only a pointer is stored in the table. The row size can therefore be reduced by using Text fields in place of Short Text fields. The downside of this approach is that Text fields are slower to search, and indexes on them are less efficient. - Set Short Text fields to the smallest reasonable maximum length. For example, for a field intended to hold a first name, you might specify a maximum length of 20, rather than accepting the default of 70.
- For History, only track the fields that really need to be tracked.
Size Considerations by Field Type
The following table details the correspondence between the
| Companyname |
|---|
Field Type | MySQL Datatype | Size in bytes | Comments |
|---|---|---|---|
varchar(70) | Maximum field length * 3 + 2 Default: 212 | Maximum field length is specified by admin, default is 70 | |
Text | mediumtext | 11 | |
Telephone/fax | varchar(70) | Maximum field length * 3 + 2 Default: 212 | Maximum field length is specified by admin, default is 70 |
Type of the SW object | bigint(20) | 8 | |
Date/Time | datetime | 8 | |
Short Text | varchar(255) | Maximum field length * 3+2 Default: 212 | Maximum field length is specified by admin, default is 70 |
Append Only Text | mediumtext | 11 | |
Password | varchar(128) | Maximum field length * 3 + 2 Default: 386 | Maximum field length is specified by admin, default is 128 |
Integer | int(10) | 4 | |
Currency | double | 8 | |
Email Pager | varchar(70) | Maximum field length * 3 + 2 Default: 212 | Maximum field length is specified by admin, default is 70 |
Elapsed Time | bigint(20) | 8 | |
File With Versioning | mediumtext | 11 | |
Floating point | double | 8 | |
Heat Bar | int(10) | 4 | |
Image with Versioning | mediumtext | 11 | |
Linked Logical Name | bigint(20) | 8 | |
Long Integer | bigint(20) | 8 | |
Percentage | double | 8 | |
Time | time | 3 | |
URL | mediumtext | 11 | |
Variable Formula | mediumtext | 11 | |
Singleton check | int(10) | 4 | |
Windows management instrumentation field | mediumtext | 11 | |
Multi Choice | varchar(255) | 255 * 3 + 2 = 767 | |
Choice | integer | 4 | |
Linked Field (MVE+fast search) | mediumtext | 11 * number of imported fields | |
Linked Field (MVE+no fast search) | 0 | No physical column created in the viewable table | |
Linked Field(other types) | Source field length | Same data type and length as the source field | |
Compound | varchar(x) | Maximum field length * 3+2 Default: 299 | Maximum field length is specified by admin, default is 99 |
Calculated Result | double | 8 | |
Communications Search Result | 0 | No physical column created in the viewable table | |
History | 2 * length of tracked fields | No physical column created in the viewable table, but History stores the value of tracked fields, before and after each edit, in an internal table. This internal table may encounter the 64K limit if a lot of fields are tracked. | |
Action Button | 0 | No physical column created in the viewable table |
MSSQL Size Considerations
Microsoft SQL Server has a limitation of 8060 on row size, but as detailed below, it keeps more of the data in internal tables.
Agiloft Datatype | MSSQL Datatype | MSSQL size in bytes | Comments |
|---|---|---|---|
nvarchar(70) | Max(2, Min(26, Length specified by admin)) Default: 26 | Length specified by admin + 2, but if the specified length is greater than 24, only 26 bytes are used in the visible table | |
Text | ntext | 16 | |
Telephone/fax | nvarchar(70) | Max(2, Min(26, Length specified by admin)) Default: 26 | Length specified by admin + 2, but if the specified length is greater than 24, only 26 bytes are used in the visible table |
Type of the SW object | bigint | 8 | |
Date/Time | datetime | 8 | |
Short Text | nvarchar(70) | Max(2, Min(26, Length specified by admin)) Default: 26 | Length specified by admin + 2, but if the specified length is greater than 24, only 26 bytes are used in the visible table |
Append Only Text | ntext | 16 | |
Password | nvarchar(128) | Max(2, Min(26, Length specified by admin)) Default: 26 | Length specified by admin + 2, but if the specified length is greater than 24, only 26 bytes are used in the visible table |
Integer | int | 4 | |
Currency | float | 8 | |
Email Pager | nvarchar(70) | Max(2, Min(26, Length specified by admin)) Default: 26 | Length specified by admin + 2, but if the specified length is greater than 24, only 26 bytes are used in the visible table |
Elapsed Time | bigint | 8 | |
File With Versioning | ntext | 16 | |
Floating point | float | 8 | |
Heat Bar | int | 4 | |
Image with Versioning | ntext | 16 | |
Linked Logical Name | bigint | 8 | |
Long Integer | bigint | 8 | |
Percentage | float | 8 | |
Time | datetime | 8 | |
URL | ntext | 16 | |
Variable Formula | ntext | 16 | |
Singleton check | int | 4 | |
Windows management instrumentation field | ntext | 16 | |
Multi Choice | nvarchar(255) | 26 | |
Choice | int | 4 | |
Linked Field (MVE+fast search) | ntext | 16 | |
Linked Field (MVE+no fast search) | 0 | No physical column created in the viewable table | |
Linked Field(other types) | Source field length | Same data type and length as the source field | |
Compound | nvarchar(99) | Max(2, Min(26, Length specified by admin)) Default: 26 | Length specified by admin + 2, but if the specified length is greater than 24, only 26 bytes are used in the visible table |
Calculated Result | float | 8 | |
Communications Search Result | 0 | No physical column created in the viewable table | |
History | 2 * length of tracked fields | No physical column created in the viewable table, but History stores the value of tracked fields, before and after each edit, in an internal table. This internal table may encounter the 8060 byte limit if a lot of fields are tracked. | |
Action Button | 0 | No physical column created in the viewable table |