[SOLVED] Less space.

[seventheyejosh]

Just a quick question.

 

Say I have 10,000 rows, or any number to be honest, in my database.

 

if i have, say,

 

id int 11

f1 varchar 255

f2 varchar 255

f3 varchar 255

 

and i don't fill up the 255 on each entry, such as inserting with values of ('0','ok',ok2','josh') and so on, would it take up more space than say a db with 10k rows of this structure:

 

id int 11

stuff text

 

with each entry of like 300 characters? or 1000 characters? like insert ('0','omgomgomgomgom')?

 

what would leave the lighter footprint.

 

basically i want to store pieces of the $_SERVER variable, but was wondering if a setup with a row for each was more heavy than a single column for the whole variable. like

 

id int 11

server_var text

 

vs.

 

id int 11

request_uri varchar 255

http_referer varchar 255

 

etc etc.

 

thoughts?

 

Thanks much!

[corbin]

A varchar takes up a varying amount of space.

 

 

A varchar(n) (the actual data part, not the structure part) takes between 0 and n*x bytes where x is the number of bytes per character in the character encoding.  (In the case of something like UTF8 it's not that simple since it can be 1 or 2 bytes, but hopefully you get the point.)

 

 

Text fields are essentially the same way.

 

 

The better separated data is though, the easier it is to manipulate/search.  (Within reason of course.)

[DavidAM]

Uh, I always thought a text field was more like a blob than a varchar.  Other DB's I've worked with allocate space for the BLOB/TEXT columns in 2K chunks.  Which means storing a short string in a text column actually wastes space.  In any case, your final suggestion

id int 11

request_uri varchar 255

http_referer varchar 255

would be the preferable way to store this data unless there is some very compelling argument for a text column.

[seventheyejosh]

kk those were the answers I suspected. Just needed to double check

[PFMaBiSmAd]

Data Type Storage Required

CHAR(M) M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set

BINARY(M) M bytes, 0 <= M <= 255

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 < 28

BLOB, TEXT L + 2 bytes, where L < 216

MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 224

LONGBLOB, LONGTEXT L + 4 bytes, where L < 232

ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)

SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

 

[fenway]

Right out of the refman, too.

[DavidAM]

Well, I stand corrected and I learned something new today .  Thanks, I'll have to remember that.