4

Does varchar and Text affect the database size? Also, do they differ in speed?

I have a Database that will have at least 4000 to 8000 new rows per day. I'm quite worried about how it performs. This is my first time setting up an Enterprise-grade system and database though.

3

Use what you need and don't worry about performance. But use only what you need. There are minor inefficiencies in saying TEXT when VARCHAR(255) will suffice. Ditto for VARCHAR(255) when VARCHAR(20) will suffice.

Here's why: If a SELECT needs build a tmp table (eg, for ORDER BY), it first tries to use a MEMORY tmp table (better), but may have to fall back on a MyISAM table (slower). Any kind of TEXT or BLOB forces MyISAM. VARCHAR turns into CHAR (for the tmp table); if the tmp table gets too big for MEMORY, it converts to MyISAM.

TINYTEXT is probably never a good idea.

A related issue: CHAR(...) is useful only if the field is (1) constant length, and (2) declared with the appropriate CHARACTER SET. A common mistake is putting a fixed-length hex string in a utf8 CHAR field.

VARCHAR and TEXT (all sizes) have a small length field prefixing them. This is relatively insignificant.

| improve this answer | |
0

I found a good explanation for your question on: http://forums.mysql.com/read.php?24,105964

| improve this answer | |
0

Normally text is appropriate for the huge content items such as blog post, since it is capped at 65,535 bytes and varchar for a teaser, since it is normally capped at 255 bytes.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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