• From: Tilburg, Netherlands
  • Registered: 2006-02-04
  • Posts: 322

Topic: which is faster, 'text' or 'varchar'

I have a 2 tables in my mod with various fields in them:
table: links_category

Field      Type       Null      Default       Links to      Comments      MIME
id         int(11)    no                         
category   text       yes       NULL                   
column     int(1)     yes       NULL                   
height     int(11)    yes       NULL                   
image      text       No                         
order      text       No

`category` contains a category name.
`image` contains a name of an image, star.png
`order` contains something link `links` DESC.
table: links_links

Field      Type       Null      Default       Links to      Comments      MIME
id         int(11)    No                         
name       text       Yes       NULL                   
url        text       Yes       NULL                   
title_tag  text       No                         
category   int(11)    Yes       NULL                   
target     text       Yes       NULL                   
image      text       No                         
show       int(1)     Yes       NULL                   
hits       int(100)   No                         
added      int(10)    No

`name` contains the name of the link
`url` contains the url of a site
`target` contains either _blank or _self

I was wondering, which is faster!
is it best to leave it all as text or is it better to go with varchar.

anyone has any thoughts about it.

kierownik's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: which is faster, 'text' or 'varchar'

What do you mean by faster?

  • From: Tilburg, Netherlands
  • Registered: 2006-02-04
  • Posts: 322

Re: which is faster, 'text' or 'varchar'

In reading the data and then display the data on the screen. I see that punbb uses both in there tables.

kierownik's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: which is faster, 'text' or 'varchar'

http://dev.mysql.com/doc/refman/5.0/en/char.html
http://dev.mysql.com/doc/refman/5.0/en/blob.html
It has nothing to do with "speed" and everything to do with the amount of data being stored.

  • From: Tilburg, Netherlands
  • Registered: 2006-02-04
  • Posts: 322

Re: which is faster, 'text' or 'varchar'

so for what i am using it is alright to go with text instead of varchar?

kierownik's Website

  • Registered: 2004-05-24
  • Posts: 449

Re: which is faster, 'text' or 'varchar'

I'm not a MySQL expert so don't take my words too seriously but...
TEXT by default is 65536 bytes while varchar can be adjustable. I'd go for varchar if you're sure you won't ever use more than a given amount of bytes you set.

  • Jansson
  • Former PunBB Developer
  • Offline
  • From: Sweden
  • Registered: 2003-01-06
  • Posts: 856

Re: which is faster, 'text' or 'varchar'

Maximum length of VARCHAR is 255 bytes, while max length of TEXT is 65536 bytes. They both have different purposes.

  • From: Tilburg, Netherlands
  • Registered: 2006-02-04
  • Posts: 322

Re: which is faster, 'text' or 'varchar'

So if i know for instance that target is not going to be larger than 6 characters, it is best to use varchar(6). Otherwise use text if i do not know the real length of it?

kierownik's Website

  • Registered: 2004-05-24
  • Posts: 449

Re: which is faster, 'text' or 'varchar'

kierownik wrote:

So if i know for instance that target is not going to be larger than 6 characters, it is best to use varchar(6). Otherwise use text if i do not know the real length of it?

Yes. varchar is great for things like IPv4, they can't exceed 15 characters (12 numbers and 3 dots) so I'd always use varchar(15) to store IPs instead of TEXT.

  • From: Tilburg, Netherlands
  • Registered: 2006-02-04
  • Posts: 322

Re: which is faster, 'text' or 'varchar'

hmm, so it is best to use varchar, ok I am going to change everything then smile

kierownik's Website

11 Reply by stevec

  • stevec
  • Junior Member
  • Offline
  • Registered: 2007-05-02
  • Posts: 8

Re: which is faster, 'text' or 'varchar'

Hi,
I think it'll make a difference if you try to index a text field. Indexing 65000 characters across multiple records can affect server performance and therefore speed of retrieval of the information.

HTH
Steve