SEO

Hello all!
We have a little performance issue with a MySQL DB with about 120 000
records (rows). Average row size is 800 bytes and thus all DB is about
100MB big. We are wondering of ways to decrease that size.
We have some columns that currently are INTEGER and take values
between 1 and 12 (or 1-5 or 1-20). I wonder if I change this INTEGER
to SET (every number for one list item) will the average row size
decrease?
The other way I though is to use TINYINT(2) (2 is the lenght - 2
signs, right?) - will it save space in that case?
I'm not sure of how many bytes each data type takes. Does that depend
on the filesystem cluster size or some varing value like that? Becouse
I didn't find anything exact info about that.

The main question for now is will the datatype change decrease size of
the DB?

Thank you.

ivanatora@gmail.com wrote:
> Hello all!
> We have a little performance issue with a MySQL DB with about 120 000
> records (rows). Average row size is 800 bytes and thus all DB is about
> 100MB big. We are wondering of ways to decrease that size.
> We have some columns that currently are INTEGER and take values
> between 1 and 12 (or 1-5 or 1-20). I wonder if I change this INTEGER
> to SET (every number for one list item) will the average row size
> decrease?
ENUM would be better than SET, but I would go with TINYINT.

> The other way I though is to use TINYINT(2) (2 is the lenght - 2
> signs, right?)
Wrong.

> - will it save space in that case?
It will save space because it is of type TINYINT rather than INT. The (2)
only affects display.
> I'm not sure of how many bytes each data type takes.
I can highly recommend a book called "The Manual" for information such as
this. Particularly the section entitled "Data Type Storage Requirements".
The name sort of gives a clue to the contents.
http://dev.mysql.com/doc/refman/5.0/...uirements.html

> Does that depend
> on the filesystem cluster size or some varing value like that? Becouse
> I didn't find anything exact info about that.
See above URL

>
> The main question for now is will the datatype change decrease size of
> the DB?
Sounds likely. But from your figures, you will save 360000 bytes for each
column you change. If there are 3 columns in this situation you will save a
collosal 1Mb or 1% of the total size.

>
> Thank you.

Thanks for the link! Ugh, I was sure I've been at that manual not a
long time ago...
So a change from INT to TINYINT would save 4-1=3 bytes. We have to do
that for 3 columns so that make 9 bytes less for each row. If there
are 120k rows that makes about 1MB less. Doesn't seem so impressive
when there is 100MB database but is a good start
Some optimization is always better than no optimization.
I didn't understood what exactly INT(2) means? A number with 2 signs,
taking 2 bytes or it can display a number with 2 signs maximum taking
all 4 bytes as regular INT?

ivanatora@gmail.com wrote:
> Thanks for the link! Ugh, I was sure I've been at that manual not a
> long time ago...
> So a change from INT to TINYINT would save 4-1=3 bytes. We have to do
> that for 3 columns so that make 9 bytes less for each row. If there
> are 120k rows that makes about 1MB less. Doesn't seem so impressive
> when there is 100MB database but is a good start
> Some optimization is always better than no optimization.
> I didn't understood what exactly INT(2) means? A number with 2 signs,
> taking 2 bytes or it can display a number with 2 signs maximum taking
> all 4 bytes as regular INT?

Here I have to refer you once again to that fount of knowledge "the manual".

In the same chapter, but an earlier section
http://dev.mysql.com/doc/refman/5.0/...-overview.html at the
very beginning it says:
A summary of the numeric data types follows. For additional information, see
Section 11.2, "Numeric Types". Storage requirements are given in Section
11.5, "Data Type Storage Requirements".
M indicates the maximum display width for integer types. The maximum legal
display width is 255. Display width is unrelated to the range of values a
type can contain, as described in Section 11.2, "Numeric Types". For
floating-point and fixed-point types, M is the total number of digits that
can be stored.

As to signs, the manual says in this very section that numeric data types
can be signed or unsigned and that this option merely affects the range of
numbers that can be stored rather than the amount of space they take up.

People may think me overly sarcastic when I point out that others should
read the manual. But most of the questions that I answer in this forum, I do
by searching the manual myself, just because those others are too lazy to do
it themselves (I'm sure this doesn't apply to you!)

Thanks again for the link.
I don't mind reading manuals, but sometimes they are too vast to be
understood by one pass or in a limited amount of time.
And in certain cases there is always something behind the manual that
can be learnt from the people's experience.
Anyway thanks to you I think I've found what I've been looking for.

Best regards,
Ivan.