SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Fastest way to UPDATE in mysql

    I have a table_1 with 10.000 rows to update and a temp_table with values for each row in table_1 to be updated.

    I have made a "for" statement in PHP and done 10.000 seperate UPDATES, but things take forever(over 30 min). And if there were 50.000 rows ... !!!


    So is there any fastest way in mysql do the updates?

  2. #2
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you talking about 10,000 or 10? Regardless, I don't think it should take half an hour. Anyway, could you give us some of the coding you are using? If that's not the problem, then something is probably wrong with the way your software is set up.
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  3. #3
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aes, lots of European countries use the period instead of the comma for notating the thousands place.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  4. #4
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can see it taking a long time, but that does seem pretty long.

    One of my scritps took an hour and a half once had over a 100,000 queries

  5. #5
    Probably eating pie mitsubishi's Avatar
    Join Date
    Sep 2001
    Location
    England, UK
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The longest execution time for a programme of mine was 18 hours and 27 minutes. It was when the national lottery first started in th UK and I wrote a little programme in BASIC on my amstrad cpc464 which worked out the probabilty of the jackpot by a series of nested loops. Probably the most inefficient programme ever written, because it can of course be done with 49!/(6!(49-6)!) but it got the right answer.

  6. #6
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dunno much about UPDATE queries in terms of optimisation, but INSERTs can be very fast, as in split second.

    See if you can alter you script to use INSERT instead of UPDATE.

    In INSERT, you can insert multiple rows using one query only, in the format

    INSERT INTO $table (apple, orange, banana) VALUES ('apple1', 'orange1', 'banana1'), ('apple2', 'orange2', 'banana2'), ('apple3', 'orange3', 'banana3');

    Or you can use LOAD DATA INFILE, which uses data from a comma delimited file.

  7. #7
    This is all Mumbo Jumbo Amit's Avatar
    Join Date
    Oct 2001
    Location
    New Delhi, India
    Posts
    277
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If the updates you are making are of Low priority you can try this also ...


    UPDATE LOW_PRIORITY ........


    This may speed up things a bit but beware ... as the manual say ...

    If you specify the keyword LOW_PRIORITY, execution of the UPDATE is delayed until no other clients are reading from the table.


Bookmarks

Posting Permissions

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