Professional Web Applications Themes

Optimizing by replacing INTEGER with SET - MySQL

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 ...

  1. #1

    Default Optimizing by replacing INTEGER with SET

    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 Guest

  2. #2

    Default Re: Optimizing by replacing INTEGER with SET

    com wrote: 
    ENUM would be better than SET, but I would go with TINYINT.
     
    Wrong.
     
    It will save space because it is of type TINYINT rather than INT. The (2)
    only affects display. 
    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/en/storage-requirements.html
     
    See above URL
     
    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.
     



    Paul Guest

  3. #3

    Default Re: Optimizing by replacing INTEGER with SET

    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 :D
    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 Guest

  4. #4

    Default Re: Optimizing by replacing INTEGER with SET

    com wrote: 

    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/en/numeric-type-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!)


    Paul Guest

  5. #5

    Default Re: Optimizing by replacing INTEGER with SET

    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.

    ivanatora@gmail.com Guest

Similar Threads

  1. Replies: 0
    Last Post: October 27th, 11:19 AM
  2. Replies: 0
    Last Post: October 27th, 11:13 AM
  3. Optimizing
    By Campbell in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: October 5th, 01:51 AM
  4. Optimizing PDF
    By Adrian Pinderhughes in forum Adobe Acrobat Windows
    Replies: 4
    Last Post: May 19th, 03:36 PM
  5. Convert Char-->integer or Integer-->char
    By victor in forum Informix
    Replies: 13
    Last Post: December 10th, 06:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139