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?
