Disk space difference about text and varchar

Question

There is some disk space difference from these 2 fields: TEXT and VARCHAR ?

I need to use a field to store URL but my hosting support varchar up to 333 chars.

Here is my table:

  CREATE TABLE IF NOT EXISTS `urls` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `n_id` int unsigned NOT NULL DEFAULT '0',
  `first_citizen_id` int unsigned NOT NULL DEFAULT '0',
  `title_citizen` varchar(128) NOT NULL,
  `title_source` varchar(255) NOT NULL,
  `link` varchar(333) NOT NULL DEFAULT '',
  `link_image` varchar(333) NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `link` (`link`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Explain exactly what you are doing, what you want to do, and give us the exact error message you get. — Jocelyn, Mar 25 '13 at 11:49
I need to use a field to store URL but my hosting support varchar up to 333chars. So I could use a TEXT field but maybe it use much more disk space and in this case it is not a good solution for me. That's all :) — xRobot, Mar 25 '13 at 12:18
How has your hosting service managed to restrict the maximum size of VARCHAR, which is 65,535? Did they compile a custom modified MySQL build just to annoy customers? — Álvaro González, Mar 25 '13 at 12:27
I don't know what is the problem but I get this error: "#1071 - Specified key was too long; max key length is 1000 bytes" . Above I have inserted the table, please see it :( — xRobot, Mar 25 '13 at 13:21

Álvaro González · Answer 1 · 2013-03-25T12:31:42.840

1

From the Data Type Storage Requirements chapter:

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

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

BLOB, TEXT L + 2 bytes, where L < 2¹⁶

Whatever, I'm pretty sure that your hosting service is not limiting anything. You probably have a table with lots of large columns and just hit the maximum row size.

edited Mar 25 '13 at 12:31

answered Mar 25 '13 at 12:26

Álvaro González

142,137
41
261
360

you right :D but now how can I use a varchar(1000) field for example ? – xRobot Mar 25 '13 at 13:12
I have insert the table above, please see it :( – xRobot Mar 25 '13 at 13:21
@xRobot - So, what happens exactly when you create a table where `varchar(333)` is replaced with e.g. `varchar(2048)`? – Álvaro González Mar 25 '13 at 15:04

score 0 · Answer 2 · answered Mar 25 '13 at 13:59

0

Yes there is Text is a pointer to a system table that holds it and other types of Blob (Binary Large OBject). So you have the overhead of the pointer, 4 or 8 bytes per not null text field.

answered Mar 25 '13 at 13:59

Tony Hopkinson

20,172
3
31
39

Disk space difference about text and varchar

2 Answers2