SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    525
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Altering column specification/indexes on large table

    I've got a table with about two and a half million records in. One of the columns in the table is ipAddress which seems to be stored as a varchar(50). why I did that I do not know!

    I understand that alter table will make a copy of the entire table. So if I changed the field to a varchar(15) it'd have a massive CPU and disk usage issue. How about if I just changed the index to length 15? Would that have to copy the whole table, or just that one index? And would using this shorter key improve efficiency?

    I know it's more efficient to store ip addresses as unsigned ints, but I don't want to have to change too much right now if I don't have to.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there is no need to change VARCHAR(50) to VARCHAR(15)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    525
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there is no need to change VARCHAR(50) to VARCHAR(15)
    So a shorter index wouldn't make queries using that index more efficient?

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    It's not a good idea to change it to 15 anyway since we'll probably all be switching to IPv6 addresses before long (since we've exhausted the IPv4 address pool), and those addresses can be longer than 15 characters

    Also see IPv6 - Wikipedia, the free encyclopedia
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    So a shorter index wouldn't make queries using that index more efficient?
    if the index were actually shorter, i would have to say yes

    but wouldn't the indexes actually be the same size? they contain the same (short) values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    525
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    So the length of the index is based purely on the length of the content?
    I was just checking a EXPLAIN statement, which said that the length of the key was 50. Red herring?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    Red herring?
    dunno... i would have to test it
    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
  •