Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

When setting up a database I want to use the most efficient data type for potentially fairly long data. Currently my project is to store song titles and thoughts pertaining to that song. Some titles might be 5 characters or longer than 100 characters and the thoughts could run pretty long.

Is it more efficient to use a varchar set to 8000 or to use a blob? Is using a blob the same as a varchar, in that there is a set size it is allocated regardless of what it holds? or is it just a pointer and it doesn't really use much space on the table? Is there a certain set size of a blob in KB or is it expandable?

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

I would suggest using TEXT over BLOB.

Primary Difference

  • TEXT and BLOB is stored off the table with the table just having a pointer to the location of the actual storage.

  • VARCHAR is stored inline with the table.

Primary Guideline

  1. Text format messages should almost always be stored as TEXT (they end up being arbitrarily long)

  2. String attributes should be stored as VARCHAR (the destination user name, the subject, etc...).


To choose on when to use VARCHAR and when to use TEXT has been discussed extensively on Stack overflow in these threads:

  1. MySQL: Large VARCHAR vs. TEXT?

  2. MySQL varchar(2000) vs text?

There is also a performance comparison thread for this on MySQL forum.

share|improve this answer
    
Thank you for those links. They answered some of the questions I had, and I did end up using a TEXT. It wasn't exactly what I was looking for as far as the physical space it takes up on the server, but I guess as long as it is only going to be a small project it doesn't matter. Later if I start to do larger projects I will need to find out though. –  BillyNair Jul 1 '12 at 2:41
add comment

I think you should use blob only if you have complex data type serialized, that is if you want to store in a single table a complex class. If you only want to store a String (or something equivalent) do not use blob.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.