Re: CHAR vs TEXT and fast Row Updates

[Pooly]

Hi,

2006/1/17, Karl Pielorz <[EMAIL PROTECTED]>:
>
> Hi All,

> 1. Fixed length CHAR fields are quicker to update than VARCHAR fields
> (because the field size is constant)

There is no advantage if not all your field in your table are fixed
size. as soon as you add a text/blob column, you loose the fixed row
length.
Keep in mind that index will also be fixed-length, and it can be more
efficient to have varchar to have quick select.


> Do we get any 'saving' by using a TEXT field, and pre-populating this with
> say 2K of 'spaces' when we create the 5,000 rows - and then ensuring that
> the UPDATE operation always writes 2K of text to the field? - e.g. will
> this avoid MySQL having to 'free up' the space for the field, then
> re-allocate 2K again for it.
>

space is not reclaimed after deletion until you run an : optimize table.

--
Pooly
Webzine Rock : http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

CHAR vs TEXT and fast Row Updates

[Karl Pielorz]

Hi All,

We have an application that needs to update rows in the database 'as fast 
and efficiently' as possible.


To this end, we've tried re-designing the table - can someone clarify the 
following assumptions we're about to make?



1. Fixed length CHAR fields are quicker to update than VARCHAR fields 
(because the field size is constant)


2. If we 'pre-create' 5,000 records in a table - and run an UPDATE on this, 
to 'replace' records in the table - it will be faster than if we run a 
'DELETE' followed by 'INSERT' operation on the table (to keep the number of 
records to 5,000)



The only other query I have is regarding TEXT fields. Some of the data in 
the table isn't big enough to be held in a CHAR/VARCHAR column (i.e. it's 
over 255 characters).


Do we get any 'saving' by using a TEXT field, and pre-populating this with 
say 2K of 'spaces' when we create the 5,000 rows - and then ensuring that 
the UPDATE operation always writes 2K of text to the field? - e.g. will 
this avoid MySQL having to 'free up' the space for the field, then 
re-allocate 2K again for it.


Or is it not that 'clever'?

I realise the savings from the above could be fairly small, but when you're 
processing thousands of updates an hour, it all adds up - if we can have 
MySQL doing 'less work' for the updates.



Thanks in advance,

-Karl

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]