Does a MySql database get padded out?

Hi all,
I'm designing a database for use in a program, and I am worried about
the size ramifications. Basically, I'd like to store text in one of the
database columns; this text will typically be ~a few thousand bytes,
but could conceivably be a few million. If I was to declare the type of
this column as MEDIUM TEXT, this would be big enough to allow for all
sizes of character strings.
However, if I only have a few entries at first that are a few thousand
characters, would the unused space in the cell be padded out to fill it
up to MEDIUM TEXT size? And would it do this if I had 2 records of a
thousand characters, and one of size MEDIUM TEXT?

TIA

Paul

trashman.horlicks@btinternet.com wrote:
> Hi all,
> I'm designing a database for use in a program, and I am worried about
> the size ramifications. Basically, I'd like to store text in one of the
> database columns; this text will typically be ~a few thousand bytes,
> but could conceivably be a few million. If I was to declare the type of
> this column as MEDIUM TEXT, this would be big enough to allow for all
> sizes of character strings.
> However, if I only have a few entries at first that are a few thousand
> characters, would the unused space in the cell be padded out to fill it
> up to MEDIUM TEXT size? And would it do this if I had 2 records of a
> thousand characters, and one of size MEDIUM TEXT?
>
> TIA
>
> Paul
>

Paul,

The data will take only as much space as is required.

The only problem you can run into is when you update a row with new text
which is larger. In that case the old row will be marked invalid and a
new row written. So when the table starts taking up too much space you
will want to compress it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 24 Jan, 17:03, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> trashman.horli...@btinternet.com wrote:
> > Hi all,
> > I'm designing a database for use in a program, and I am worried about
> > the size ramifications. Basically, I'd like to store text in one of the
> > database columns; this text will typically be ~a few thousand bytes,
> > but could conceivably be a few million. If I was to declare the type of
> > this column as MEDIUM TEXT, this would be big enough to allow for all
> > sizes of character strings.
> > However, if I only have a few entries at first that are a few thousand
> > characters, would the unused space in the cell be padded out to fill it
> > up to MEDIUM TEXT size? And would it do this if I had 2 records of a
> > thousand characters, and one of size MEDIUM TEXT?
>
> > TIA
>
> > PaulPaul,
>
> The data will take only as much space as is required.
>
> The only problem you can run into is when you update a row with new text
> which is larger. In that case the old row will be marked invalid and a
> new row written. So when the table starts taking up too much space you
> will want to compress it.

Thanks!
So, using a simple example, if I have a table with
1 xxxxxxx 2 3 4

with the maximum size of the second cell being (say) 10 and I want to
update it with yyyyyyyyyyy (or 11 characters, so there is an overflow)
will I get
1 yyyyyyyyyy 2 3 4
2 y ? ? ?

TIA

Paul

trashman.horlicks@btinternet.com wrote:
>
> On 24 Jan, 17:03, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> trashman.horli...@btinternet.com wrote:
>>> Hi all,
>>> I'm designing a database for use in a program, and I am worried about
>>> the size ramifications. Basically, I'd like to store text in one of the
>>> database columns; this text will typically be ~a few thousand bytes,
>>> but could conceivably be a few million. If I was to declare the type of
>>> this column as MEDIUM TEXT, this would be big enough to allow for all
>>> sizes of character strings.
>>> However, if I only have a few entries at first that are a few thousand
>>> characters, would the unused space in the cell be padded out to fill it
>>> up to MEDIUM TEXT size? And would it do this if I had 2 records of a
>>> thousand characters, and one of size MEDIUM TEXT?
>>> TIA
>>> PaulPaul,
>> The data will take only as much space as is required.
>>
>> The only problem you can run into is when you update a row with new text
>> which is larger. In that case the old row will be marked invalid and a
>> new row written. So when the table starts taking up too much space you
>> will want to compress it.
>
> Thanks!
> So, using a simple example, if I have a table with
> 1 xxxxxxx 2 3 4
>
> with the maximum size of the second cell being (say) 10 and I want to
> update it with yyyyyyyyyyy (or 11 characters, so there is an overflow)
> will I get
> 1 yyyyyyyyyy 2 3 4
> 2 y ? ? ?
>
> TIA
>
> Paul
>

No, the original row will get marked completely invalid and the new row
will be added at the end of the table. The old location of the row is
now available for reallocation when another row comes along.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================