SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Changing columntype + influence of columntype on sorting times ?

    I have a table where I log all kind of actions that happen on my site. I was probably in a hurry when I made that table because it isnt really well build. There is a cell that is used to store the unix timestamp of that action, and its set as a VARCHAR(20) . After some research I concluded that a INT(10) would be more suited. There are a few hundredthousands entrys in that table and they are quite important, so I'm not so keen on just changing that fieldtype without knowing what might happen.

    2 simple questions :

    - Will it influence any of the old data if I change the fieldtype from varchar to int ? This change looks like a one-way operation if things go wrong :s

    - Am I right to assume that with an INT type those rows can be sorted faster ?

  2. #2
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I doubt it will influence the old data. I have tried with a small table and the fields were properly converted.
    One problem could arise because of the varying lengths of the two column types:

    Use

    Code:
    SELECT MAX(Length(timestampcolumn)) 
    FROM yourtable;
    to make sure that 10 is sufficient.

    You could test if the conversion goes successfully using:

    Code:
    CREATE TABLE backupoforiginal AS SELECT * FROM originaltable;
    Then you can change the column type in that table. If anything goes wrong you still have your original data.

    I don't know if using int increases the performance of the search process. However, you will get properly ordered results using int.

  3. #3
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I belive searching int columns in generally faster, don't quote me on that though...
    - website


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
  •