SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2000
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Why TEST when you have MEDIUMTEXT?

    I am learning of the different types of colum types in MySQL and I don't understand why there are different vesions of most of them. Like TEXT, which has a maximum length of 65535 characters and MEDIUMTEXT, which has a a maximum length of 16777215 characters. (And then there is LONGTEXT as well.)

    What is the point of them? Why not just always use MEDIUMTEXT or LONGTEXT, so you'll be sure never hit a wall with to many charactrs. The same tthing goes for TINYINT/SMALLINT/MEDIUMINT/INT etc.

    Can someone explain it to me?

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it may not be as big of a deal with the TEXT types (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT), but with the INTs (TINYINT, MEDIUMINT, INT, BIGINT) it definitely is (to me). the reason? the larger types take more space.

    TEXT = length + 2 bytes
    MEDIUMTEXT = length + 3 bytes
    LONGTEXT = length + 4 bytes

    now, 2 bytes isn't that much compared to your (presumably) long string of text. but, still, why use it if you don't need it? you should always use the samllest type that you need (kinda like you should always make your columns NOT NULL, unless you need NULL).

    with the INT types, it's more of an issue.

    TINYINT = 1 byte
    SMALLINT = 2 bytes
    INT = 3 bytes
    BIGINT = 4 bytes

    the smaller they are, the less space they take up. and the smaller they are, their values can be compared faster. most importantly, though, since the INT types are often used as keys, the smaller they are, the more rows that can be held in MySQL's index cache (= less disk reads). and when you do need disk seeks to find a row in the index, there will be less if your values are smaller. also, the smaller your data types, the less data MySQL has to write after write operations.

    so it comes down to storage space, efficiency, and speed.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Evangelist
    Join Date
    Oct 2000
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, ok, now ít makes sense

    Thank you very much for the explanation!


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
  •