List:General Discussion« Previous MessageNext Message »
From:Karl Pielorz Date:January 17 2006 10:46am
Subject:CHAR vs TEXT and fast Row Updates
View as plain text  
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,

CHAR vs TEXT and fast Row UpdatesKarl Pielorz17 Jan
  • Re: CHAR vs TEXT and fast Row UpdatesPooly17 Jan