SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot Offspring2099's Avatar
    Join Date
    May 2005
    Location
    Los Angeles, CA
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Copy large table

    I don't know where to ask this in PHP or here....anyways,


    I have a table with 66K records. I have a script, that:

    1. Creates a new table.
    2. Does a 'select *' statement on the old table.
    3. Then loops row by row and inserts the old records into new table ( need to do it this way because, I can't just straight up copy the old table, I need parts of it, with some columns having new names)

    The problem is this. If I copy 5K rows, this runs fine. If I do 30K rows, I see that some rows get dumped. Or never copied. So I figured some buffer must be getting full, so I set up a timeout in the script for about 3 minutes for every 3K rows. This solved the issue for 30K rows, but now I have to do 60K and the timeout method is not working.

    Is there an ideal method to copy big tables (remember I have to get parts of the old table and rename the columns, I can't just copy a table). Also am I right, could a buffer get too much data and just be overflowed? Anybody have this problem? I'm stuck doing things manualy for now, which is not good.

    Thanks.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    provided the structure is exactly the same:
    Code:
    insert delayed into newTable
    select * from oldTable
    but you said it isn't, so you can modify this a bit. lets say you have table a with colums a1, a2 and a3. you want to insert into table b with columns b2, b3 and b4. you want column a1 to go to column b2, etc. and you only want records where a1 > a3.
    Code:
    insert delayed into b (b2, b3, b4)
    select a1, a2, a3
    from a
    where a1 > a3
    the delayed part lets your PHP app continue on without waiting for the insert to finish. note that this means you will not be able to accurately query the new table until mysql finishes doing the delayed insert.

  3. #3
    SitePoint Zealot Offspring2099's Avatar
    Join Date
    May 2005
    Location
    Los Angeles, CA
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i will try it asap. Thanks a lot.

  4. #4
    SitePoint Zealot Offspring2099's Avatar
    Join Date
    May 2005
    Location
    Los Angeles, CA
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This worked and since it's done on the db level its 1,000,000 times faster. Wow, this shows how much I don't know about sql. Thanks again.


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
  •