Created a table with the following characteristics (see screen).
It is supposed to use the InnoDB table.
The total number of entries in the table, I think 10.0 million - 30.0 million.

In the designation "textxxxxx-000000" conventionally shown:
- "textxxxxx" - the text of the corresponding length.
- "000000" - numbers.

Example:
nomerthe-498798
scrsdgoob-654983

PRINCIPLE OF WORK WITH BASE:
1. Filling -
Entry of records through the client program: single or in batches.
"Batch" - I mean that the records are parsed from the source and get into the database using the parser program.
Or just import.
Import (or filling), first in an empty database, and then as the table is filled.

2. Operations .
2.1.0 selection of records from the table
2.1.1 By foreign key .
The number of records when sampled by a foreign key varies 1-100 pieces. Those. The maximum number of entries that can correspond to one foreign key can reach 100 pcs. records.
2.1.2 By combined conditions.
In conditions can participate from 1 to 7 fields
Depending on the field, the selection conditions may be:
- the text contains;
- id interval;
- Certain date;
- date range;
Depending on the situation, both one condition and different combinations can be used.
2.2.0 Adjustment of records .
2.2.1 Adjustment for one of the client .
The basic principle of operation involves adjusting the record one by one, using the client.
2.2.2 Batch Adjustment .
I think sometimes you may need the function "find - replace"
Example: "find - replace" text in any field for all entries.

3. Duplicates.
Excluded

Question.
Will the types of fields be optimal for a table with such a data set in order to provide:
- 1. minimum amount of disk space?
- 2. Maximum work speed when accessing a foreign key?

enter image description here

  • one
    For да/нет it да/нет better to have enum. How do you plan to store fractional numbers in the int field? decimal(7, 3) - vp_arth
  • one
    where int and the maximum value will be 1000, it is easier to set the type SMALLINT and set the length to 4 ......... where the textxxxxx-00 field does not make sense as TEXT at all ......... about the type field LONGTEXT, all the more ..... and the field with fractional values ​​(the preceding one) cannot be INT - Alexey Shimansky
  • one
    1) textxxxxx-000000 - what is in place of the x and zeros? only numbers? 2) The Цифра с максимальным значением 1000 is nonsense. Only 10 digits, and the maximum - nine. That is, if in decimal CC, by itself ... 3) The maximum speed depends not only on the structure, but not least on the content and on the operations performed. - Akina
  • one
    Now set your priorities. Either the minimum volume, or the maximum speed. Optimizing one is guaranteed to spoil the other. - Akina
  • one
    Minimizing the volume of IMHO. 1) Fields 1,2,4,7,12 - if the field length is fixed, replace VARCHAR / TEXT with CHAR. TEXT replace anyway. 2) Fields 6 and 10 are replaced by SMALLINT. 3) Replace field 11 with TEXT. 4) Field 14 - either scale by 3 orders and leave INT, or use DECIMAL. 5) Replace field 15 with ENUM ('YES', 'NO'). Speed ​​maximization Without detailed data on filling and operations it is impossible to give meaningful advice. - Akina

0