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,