Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Question Unanswered: Update recrods via Buffer (improve performence)

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    91
    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.

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    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 ?
    Originally posted by vanekl
    If you're using InnoDB or BDB tables then wrap multiple updates in one transaction.
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    91
    "...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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •