Need advice on the organization of the table (MySQL MyISAM)


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
58 views
Is added to the table about 50 thousand records a day (now about 10 million records). Each record contains 15 INT fields and one VARCHAR(32) field.

The task was to add a new field that contains the composite attribute. There are 7 types of attributes, each can take a value from 1 to 65535 (2 bytes). But each record may contain not more than 3 (or rather 0 or 1, or 3).

Only about 5% of the records will have the attribute. A large part of the query will select records by the existence of (at least some) or complete absence of attributes. Parse attributes, will occur outside the base (only display information on the attributes of the records, no search of the records by a particular attribute).

What options come to mind:
1. In the forehead. 7 UNSIGNED SMALLINT fields (a field for each attribute type). Large volume, the complexity of the sample (AT_1 > 0 OR AT_2 > AT_3 0 OR > 0 ...), but ease of use.
2. Small space saving. 3 fields 3 fields and TINYINT UNSIGNED SMALLINT (id of a pair of attribute and value). Almost the same amount, a little more than simple sampling (AT_VAL_1 > 0 OR AT_VAL_2 > 0 OR AT_VAL_3 > 0), but the need to assign each the attribute ID, plus a more complex parsing after the selection.
3. More saving, more confusion. 3 INT field (the first byte — the remaining bytes value). It is difficult to find any pluses in comparison with its previous version (only if fewer fields).
4. Easier to nowhere. One BLOB field to 14 bytes. Only one field, the most simple sampling (ATTR IS NULL), no additional confusion (each attribute has its own DC offset in the field, it is not necessary to disassemble the id).

What would you recommend?
by | 58 views

4 Answers

0 like 0 dislike
Firstly IMHO it is better to use char instead of varchar to the ROW_FORMAT of the table was fixed, it would speed up the compilation and recording. With your options I think you need to use first.
by
0 like 0 dislike
The complexity of the sample for the first option (and for the rest, except the last one) can be circumvented by the introduction of a Boolean field HAS_ATS
by
0 like 0 dislike
You write that the last option is the most simple, and has virtually no drawbacks — it and use. Way to put the indices most likely makes no sense in your situation they will only slow down the query execution.
by
0 like 0 dislike
Another option: one box — total type attributes (e.g., a bitmask), and 3 SMALLINT. Space-saving, simple sampling (ATTR_TYPES = 0), but more complicated parsing attributes. Although the method with BLOB decides!
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
4 answers
0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
110,608 questions
257,186 answers
0 comments
28,042 users