Storing typed data in a database

olegmar2013-09-23 15:30:10

MySQL

olegmar, 2013-09-23 15:30:10

Hello, maybe the title is not clear enough, but I did not know how best to indicate the essence of my question. And here she is. Quite often, the database has to store data that belongs to a certain type. Well, for example, an article can be both news and a blog post. In this case, we create a field in the database that is responsible for the type of object. So, does it make sense to assign an id to each type and insert into the database not the word "blog", but the corresponding "1". It is obvious that the word is clearer and more convenient, but what will be more profitable if there are millions of records?

Answer the question

In order to leave comments, you need to log in

7 answer(s)

Andrey Burov, 2013-09-23
@BuriK666

There is an ENUM type

Max, 2013-09-23
@AloneCoder

The number is indexed better, for understanding it is necessary to indicate comments to the columns and to the table, and in the code to define readable constants for such fields

@mgyk, 2013-09-25
_

There is not much point in such an optimization. It will only affect the size of the files on the disk, it will not affect the size of the index.
If the same table has text fields, then the difference will be completely invisible.

merlin-vrn, 2013-09-25
@merlin-vrn

Those. do you want to optimize the "record type" column, which takes several bytes (and can actually store an int there if the column is enum), while the adjacent column is of type text with a variable length of the order of several kilobytes? Well, yes, if you're lucky, you'll win some fractions of a percent.

DenKrep, 2013-09-23
@DenKrep

To be honest, I don’t know how it is implemented inside MySQL, but in Oracle there is such a situation that integer is far from one byte (and can reach up to 9, as far as I remember, though Oracle’s supported bit depth is also impressive).
As an example to think about:
CREATE TABLE TEST (ID INTEGER, vc VARCHAR2(10 CHAR)); INSERT INTO TEST VALUES (1,'1'); SELECT DUMP(ID), DUMP(vc) FROM TEST;
DUMP(ID) DUMP(VC)
1 Typ=2 Len=2: 193.2 Typ=1 Len=1: 49
Pay attention to the lengths.
And short strings are indexed for unique entries, as a rule, not worse, I disagree. It's another matter if you plan to use range predicates... This is where "moments" may arise, but I think then you wouldn't be asking your question.
Of course, all of the above is primarily applicable to Oracle, but I think that the direction of thought “to check” is understandable, but you can’t trust anyone anyway :)

Artyom Karetnikov, 2013-09-23
@art_karetnikov

Let's just think together. First, it is not necessary to use full int - how many types of articles do you have? TinyInt USIGNED - won't override? Secondly, if you have a varchar string, what is the length? If there are four characters, you may not even notice. And above - imnsho, you need to use a number.
I also assume with a high probability that you will need a selection - and not only for this field, but rather an index for several. Also, size will be important here.

zapimir, 2013-09-23
@zapimir

Obviously, the word is clearer and more convenient, but what will be more profitable

It is necessary to proceed from the fact that not a person will select records from the table. Therefore, it is better to use a data type that is smaller and more comfortable for a machine, rather than a person. I prefer TINYINT USIGNED for all types, it will be the fastest option, but if you want clarity, you can use ENUM. It is better not to use VARCHAR, because in order to read it, you must first find out its length, then read it, this is not counting the fact that comparing numbers is faster, comparing strings, and VARCHAR will take up more space, both in the table itself and in the index.