www.webdeveloper.com
Results 1 to 11 of 11

Thread: TINYTEXT vs VARCHAR - not as basic as it sounds

  1. #1
    Join Date
    Nov 2007
    Posts
    81

    TINYTEXT vs VARCHAR - not as basic as it sounds

    Hi guys,

    I've got a database field which stores customer usernames, which I limit to a maximum of 50 characters. At present I'm using a TINYTEXT field to store them, but this morning decided to switch to VARCHAR(50), because I thought it would speed things up.

    Now, I carried out a very rough speed test to see whether I'd got any improvement. What I did was go into PHPMyAdmin, go to my table and click on 'Browse' and made a note of how long it took to load the table's data. I did this 5 times and took a simple average.

    Now, when my data was stored as TINYTEXT I got an average time of 0.54ms but when I changed it to VARCHAR it went up to 1.32ms - more than double.

    Can anyone explain what's going on and which data type I should use?

    Thanks,

    Pete

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    I believe mysql's implementation of VARCHAR and TEXT are similar to the standard implementations of VARCHAR and VARCHAR2

    A varchar column always contains as many bytes as defined by it's sizing.
    A varchar2 (in this case, any of the TEXT columns) will store as much information

    See this note from mysql's website:
    Quote Originally Posted by MYSQL
    VARCHAR(M), VARBINARY(M) L + 1 bytes if column values require 0 255 bytes, L + 2 bytes if values may require more than 255 bytes
    TINYBLOB, TINYTEXT L + 1 bytes, where L < 2^8
    BLOB, TEXT L + 2 bytes, where L < 2^16
    MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 2^24
    LONGBLOB, LONGTEXT L + 4 bytes, where L < 2^32
    http://dev.mysql.com/doc/refman/5.0/...uirements.html

    Hope this helps, and feel free to use the SQL Forum for questions like this!
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Nov 2007
    Posts
    81
    Ah yes, the SQL forum, woops!

    Thanks for your reply, that seems to show that using VARCHAR(50) would be better than TINYTEXT. However, my rudimentary test seemed to show that the VARCHAR fields were slower to access than the TINYTEXT ones - is this true? If so, presumably I'd be better off sticking to TINTEXT if performance is the most important factor?

    Pete

  4. #4
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,742
    Quote Originally Posted by chazzy
    A varchar column always contains as many bytes as defined by it's sizing.
    That's not true, and that's why it is slower; the "VAR" of VARCHAR stands for variable length. CHAR columns on the other hand are always full length.

  5. #5
    Join Date
    Nov 2007
    Posts
    81
    So TINYTEXT is faster than VARCHAR, but CHAR is faster than TINYTEXT?

    Pete

  6. #6
    Join Date
    May 2007
    Location
    Cleveland, OH
    Posts
    1,403
    It just refers to the size of the bytes used by each character. Speed has many other variables to consider, character size just being one. A good way to run TRUE speed tests (not using PHPMyAdmin and hitting refresh), is to get into a shell (SSH) and run the mysqlslap tool.

  7. #7
    Join Date
    Nov 2007
    Posts
    81
    Blimey, another tool to get the hang of! Looks useful though, thanks for the link.

    Pete

  8. #8
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,742

  9. #9
    Join Date
    May 2007
    Location
    Cleveland, OH
    Posts
    1,403
    Sorry, I was thinking a SQL char is equivalent to a C++ char, and a varchar was equivalent to a wchar_t in C++, in which case it's referring to the amount of data, or bytes, used to store the character (1 vs 2). Dunno why I assumed that, lol. But my point was things like table size, table type, indexing methods, etc are probably more important to "speed" then character types.

  10. #10
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,742
    Quote Originally Posted by TJ111
    indexing methods
    That's the whole point; if the storage type is fixed length it makes reading the table a lot less work because you know where each string of data starts and finishes without looking at the data itself to find out.

  11. #11
    Join Date
    Nov 2007
    Posts
    81
    So if a fixed length string is quicker to read, presumably CHAR is superior to VARCHAR wherever database size isn't an issue? (Which I'm assuming it isn't in most cases?)

    Pete

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles