Number and Size of Records

Number of Printed Pages Per Field

All fields in the database table that can hold user input are created as TEXT/BLOB with a maximum size of 64 kilobytes. At around 3000 characters per printed page this means that each field of a record can contain around 20 pages of text. That is probably more than enough for most if not all projects. If more is needed, consider splitting the field into two independent fields. If that is not feasible, the source could be modified to create fields as LONGTEXT fields. For details see the data type description pages the MySQL documentation.

Database Engine

The more modern InnoDB database engine used by MySQL by default has an additional limitation beyond the maximum size of each database field. When a database is created with many fields (e.g. 30+) and each field contains a significant amount of text, it can be observed that the database silently discards text at the end of fields into which new text has been entered. This seems to be related to the internal data structure in which the beginning of large fields are stored in the main record while larger content is saved in overflow structures outside of the main record. MyISAM, an older database engine, does not suffer from these limitations. As a consequence, the main and diff database tables use the older MyISAM engines.

Number of Records

The number of records in the database is limited by two factors:

• MySQL surely has a limit as to the number of database records that can be stored. As MySQL is used for very large databases, it is likely to be very high. The exact number is unknown to the author but likely by orders of magnitude higher than required by this project.

• The main limit for this application is the organization of the record IDs in config/id-structure.php. MySQL stores the database id in a 32 bit signed integer which limits the usable ids from 0 to 2.147.483.647. In addition, to make reordering records easier (limiting the times ids have be reassigned when a record is inserted between two other records with consecutive IDs), the number has to be divided by 100 in the default configuration. On top of that, the ID structure might be further configured by the user to subdivide records into categories.

• Therefore, the number of records that can be held in the database mainly depends on the configuration made in config/database-id.php. Here's an example configuration and the resulting limitations:

        21 categories (out of 999 possible, 3 digits are used to identify the category)
 1.000.000 ids per category
       100 ids between each record, i.e. each category can hold 10.000 records

--> total number of records = 21 * 1.000.000 / 100 = 210.000 records

• The number above would double to 420.000 overall records and 20.000 records per category if default_increment_new_record in config.php would be reduced from 100 to 50 and any initial bulk imports would increment the id in the same way. Reducing the increment from 50 to 25 or even lower or increasing the number of categories would further increase the numbers. However, the database-id structure is flexible. If only 99 categories are required, only 2 instead of 3 digits would be required to hold the category information. This means that the space to hold ids per category increases by an order of magnitude!