RE: FN-FORUM: MySQL data types and lengths

date posted 5th January 2007 20:54

I agree with Anthony, but want to add the following:

1. Making a database larger than necessary may be OK if you are just playing
around with MySQL code, and you are the only person who needs to use it. But
- have you thought about others? How would they go about searching for
something within the database, if they were accessing it via a web site, for
example? Making it larger than is necessary is wasteful on space, and (to an
extent) takes longer to search, for those still on slow connections.

2. Using longer fields, such as TEXT, introduces an risk of users entering
data which technically does not validate correctly (e.g. numbers in a text
field). It also makes it harder for you as the database designer to then
manipulate this data using a language such as PHP; yeah - granted - I think
PHP would allow use of strings for calculations (as in this example), but is
this good practice?

3. Making the database bigger than it really needs to be reduces database
integrity, and I think ultimately leads to a higher risk of data corruption.
Would you want to have to restore a 20MB database when in reality it need
only have been 20K? (An exaggerated example, I know, but it is only to serve
a point...)

4. To take Andrew's point about data sizes, just imagine if the database
contained any BLOB fields. OK, this is a little extreme I know, but BLOB
fields can hold upto several gigabytes of data such as video or audio clips.
Now, if I had to store such files, I would probably link to them, rather
than have them in the database; if however I had to store them in the
database, just imagine the effect if I then started adding unnecessarily
large fields (as you describe) as well. Let us just say that I think the
database would not thank you for having to work unnecessarily hard, let
alone your users...

It really boils down to a matter of common sense - there is no need to make
life harder than is really necessary for databases. Databases can be
compressed if required, and will need reindexing from time to time, to
maintain peak efficiency. This is fine if the database contains a lot of
records, which are using just the space required. But if the are being
stored in fields which are longer than are necessary, then it will take
longer to do this - do you want to spend longer waiting for databases to be
reindexed, just because they were made with larger fields than necessary?

HTH,

Alex.

-----Original Message-----
From: [EMAIL REMOVED] [EMAIL REMOVED] On Behalf Of Anthony
Cartmell
Sent: 05 January 2007 20:02
To: FN-FORUM / [EMAIL REMOVED]
Subject: Re: FN-FORUM: MySQL data types and lengths


> The client asked why i couldnt make them all as large fields (TEXT) ,
> it kind of threw me as i didn't really know why, i have just done it
> out of habit.
>
> I know there is a valid reason.. somewhere. Thoughts? :)

There are some differences to do with indexing and the ability to have a
default value. A TEXT is also stored differently, which might have
performance and space implications for very large tables. A VARCHAR of less
than 255 only takes size+1 bytes for storage, a TEXT takes size+2 bytes.
Probably not an issue unless you have millions of short strings to store.

http://dev.mysql.com/doc/refman/5.0/en/blob.html
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

Cheers!

Anthony
--
www.fonant.com - Quality web sites

--
Freelancers, contractors earn more with Prosperity4 Call 0870 870 4414 or
visit www.prosperity4.com and benefit from Inland Revenue approved expenses
today.

To advertise here: http://www.freelancers.net/advertising.html