Hi,

My application is making a lot of updates which are not critical (if server crashes and I loose last few updates I'm ok). Also if these updates do not show up immediately I'm also ok.

Does MySQL offer a "low commit" or "update into buffer" way of updating records, so this will be faster and will happen when the server has time to do it... ?

Alternatively I can create an array of record IDs and values in application memory, and update this info in batch, but that would not be as good as a built in function if it exists... (Using ASP on windows 2003, MySQL 4.016)

Thx!

-Ziv.

This will perform updates on a table only when no other operations
are being performed on it:

UPDATE LOW_PRIORITY tbl_name ...

If you're using InnoDB or BDB tables then wrap multiple updates in one transaction.

If you're using MyISAM or BDB tables run 'OPTIMIZE TABLE tbl_name' on those
tables that have variable length fields.

If you want to see whether MySQL is using indexes when performing your update
then convert the UPDATE statement to a SELECT statement and use EXPLAIN on the
SELECT statement.

If the data in the tables is not terribly important and the tables are relatively
small then consider changing them to HEAP tables that completely reside in RAM
for faster INSERTs, UPDATEs, SELECTs.

Thx, Lots of good info !!! did some reading on LOW_PRIORITY...

MySQL.org manual says "...LOW_PRIORITY should normally not be used with MyISAM tables as this disables concurrent INSERTs... "
Is this also true for "UPDATE" with MyISAM ?
I read one post where person wrote if more than one LOW_PRIORITY updates have pilled up, it will auto-roll them into a single multi-INSERT statement. 1) this true? 2) works the same for UPDATESs? 3) is it still better to add UPDATES up in application memory and batch update every so often as oppsoed to use LOW_PRIORITY?

-Ziv.

"...LOW_PRIORITY should normally not be used with MyISAM tables as this disables concurrent INSERTs... "

This (ie LOW_PRIORITY) applies only to INSERTs because INSERTs affect new records at the
end of the table (unless there are holes in the table, in which case
concurrent inserts are automatically disabled then anyway).


As to your points 1) and 2) I don't know, I haven't read
anything to suggest this is true or false.

If I had the option of batching a bundle of UPDATEs into one
transaction instead of using the LOW_PRIORITY option I would
go with the transaction, just because I'm alittle apprehensive
about using non-standard SQL ops like LOW_PRIORITY, and when
I do updates I need the update to show up soon.