If I understand correctly, then the size (size) of the table that it occupies on the computer disk is affected by the type of the table, the type of fields, etc.

Question
How to calculate the volume of the table?
Are there any calculators for this purpose?

  • No, the number of your records in the table will differ from the predicted with a near-unit probability, and the resulting calculation will still be useless to save disk space, because you should always have 50% free space in case of a surge in activity. - etki
  • one
    If the record size is fixed, then you can calculate the volume of the table with a sufficiently high accuracy. NULL-fields, variable length fields, blobs - complicate the calculation. If you have a varchar(500) field, where usually 5 characters lie, then you should count exactly 5 characters, and not 500. - Mark Shevchenko
  • @Mark Shevchenko # 1. "5 characters ..." is not important text or numbers? # 2.Types of fields on the size of the table does not affect? # 3. For example, 5 characters is what volume? #Ps. I sort of understood .. probably make out as an answer ... - koverflow
  • one
    You might be interested in the theme of Stackoverflow.com/questions/269006 - cheops

1 answer 1

The exact size of the table can be calculated only if the record size is fixed. For this you need to:

  1. All fields of the record had a fixed size. char(50) always 50 bytes in size, while varchar(50) is always 1 to 51 bytes in size.

  2. The record did not contain NULL fields.

Type sizes can be found in the MySQL documentation .

The types nchar , varchar , varbinary , tinyblob , tinytext , blob , text , mediumblob , mediumtext , longblob , longtext do not have a fixed size. If the table contains fields of these types, its exact size cannot be calculated. Sometimes the size can be calculated approximately with high accuracy, if it is clear what data will be stored.

All other types have a fixed size. INT 4 bytes, it's simple. CHAR(7) 7 bytes. The size of NUMERIC(12, 3) harder to calculate because MySQL packs every 9 decimal digits into 4 bytes.

Each entry contains a service header , which has a size of 5 bytes. If the NULL field is NULL, it is not stored in the record, just set the bit in the header.

After the table size is counted in bytes, you can estimate how much space it takes on the disk. For speed, MySQL stores data in large pages, in blocks of 4 KB or 8 KB. If the record with the title is 100 bytes, and the page size is 4096 bytes, then the page will fit 40 records and there will be 96 empty bytes on each page. 400 records will occupy 10 pages on a disk or 40 Kb.

In addition to the data, the table contains indexes, each of which also takes place . If the index contains fields of variable size types, the index itself will also be of variable size.

Despite the fact that the exact size of the table is difficult to determine, in practice it is enough to evaluate the order.

Additionally

When MySQL stores text, it stores the numbers as ordinary characters. For example, in the UTF-8 encoding, all digits occupy one byte, and Russian letters occupy 2 bytes. To read correctly, you need to know what encoding is used for this field.

Types INT and so on store numbers in binary. INT always requires 4 bytes, where the integer is stored in the range -2 31 ..2 31 −1.

The DECIMAL type stores numbers in the decimal system, using clever coding to pack every 3 decimal digits in 10 bits (every 9 digits in 4 bytes).

BIT(n) types BIT(n) are packed together in a sufficient number of bytes. BIT(3) will always occupy 1 byte, because less memory cannot be allocated. But the two fields on BIT(3) will also occupy 1 byte, since MySQL will store them together.

  • Could you comment on this topic ?.stackoverflow.com/ questions/ 634210/… - koverflow
  • точный её размер посчитать невозможно - but you can estimate the maximum. - vp_arth