SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    318
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Optimise field values = better load?

    For some weird reason, whenever I create a field, I usually get it the value of 255. So, for something like memberID INT(255).

    Recently, my site has been hitting up big numbers and have been hit by lag waves. I'm wondering, as well as other things, if reducing the values of these fields helps with better server load? I'm pretty sure that the memberID field only needs int(9) so i'm not sure why I have 255 there. This is frequent in a lot of my table's fields.

    Any info would be great. Thanks :P

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you don't need a number there at all unless you are zerofilling.

    create table members
    ( member_id int not null auto_increment
    , primary key (member_id)
    ) engine =****** default charset=******

    should be fine.

    I would look into what indexing you have set up as well as the data types for each column

    bazz

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the number in parentheses behind integer columns has nothing to do with the range of numbers that the column can hold

    INT(1), INT(9), INTEGER, INTEGER(255), INT(937) -- all these are 4 bytes and hold exactly the same range of integers

    definitely look into indexing your tables for performance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    318
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Oh :X So what does the number represent?

    Thanks for the info

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    it represents the number of digits to display when using ZEROFILL

    so if you had the value 937 stored in an INT(25) column with ZEROFILL, it would display as 0000000000000000000000937
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    318
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Well I don't use Zerofill at all, so does it matter too much :X? I want to save as much work as possible XP

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if it doesn't matter, leave it off -- use TINYINT, SMALLINT, INTEGER, and BIGINT, all without a number in parentheses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •