PHP Moving Data between MySQL tables

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

I am trying to move data from one table to another but it's going extremely slow.

I have customeraddresses which has 52000 records. I want to move those records to another table (on the same DB) called clientaddresses using PHP cause I have to do some logic because customeraddresses and clientaddresses have minor DB structure changes. I pull everything by an INDEX clientID field, but is there anything I else I can do to speed up the process? I'm kicking the script off via cron on an internal webserver with a hexcore processor, 4GB of memory, and low-use.

I simply:

Code: [ Select ]
//connect to db...
$obj = object connection class.

SELECT * FROM customeraddresses

while mysql_fetch_array() {

   clientID = row[clientID];
   street = row[street];
   city = row[city];
   mailing = row[mailing]; // enum 1,2

    // small logic
      if mailing == 1 ...

 INSERT INTO clientaddresses(clientID,...) VALUES('clientID',...);


}
  1. //connect to db...
  2. $obj = object connection class.
  3. SELECT * FROM customeraddresses
  4. while mysql_fetch_array() {
  5.    clientID = row[clientID];
  6.    street = row[street];
  7.    city = row[city];
  8.    mailing = row[mailing]; // enum 1,2
  9.     // small logic
  10.       if mailing == 1 ...
  11.  INSERT INTO clientaddresses(clientID,...) VALUES('clientID',...);
  12. }



Is there any reason why this should be taking 2 hours to transfer 52k records? What are some things I can look at to speed it up such as:

Will adding a LIMIT OFFSET,ROWS to the SELECT be helpful and create another loop to change the offset?
Should I just be building the INSERT queries and save as a .sql file and then import them? Will that be any faster?
Is using OO programming for my connection and queries slowing things down? (I have a very simple class for connecting, selecting, and inserting data)

Thanks for any pointers.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9086
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

There can be a few reasons this is going extremely slow. The first is the fact that you are doing a read and write for each cycle 52000 times. So its going:

READ
WRITE
READ
WRITE
...

That is always going to be slow especially if you are using MyISAM tables and that is 104,000 separate operations.

The second reason it could be slow is how you have indexes set. First the reads could be slow if you don't have indexes, however, if you do have indexes then the read parts could be fast. On the other side, if you do have indexes then then it will make the write process take longer as after each write the index will need to be rewritten.

So my advice to you to speed this up is to do it in larger blocks. For instance maybe read the first 100 records using a limit clause. Store those in an array and then create all of the SQL you are going to use to write the next 100 records. Then once you have that created actually execute the INSERT commands. That way for each 100 records you are doing one read and one large insert.

So for example:

MYSQL Code: [ Select ]
SELECT * FROM customeraddresses
LIMIT 0,100
  1. SELECT * FROM customeraddresses
  2. LIMIT 0,100


Then for the insert something like this:

MYSQL Code: [ Select ]
INSERT INTO clientaddresses(clientID,...)
VALUES
('clientID1'...),
('clientID2'...),
('clientID3'...),
('clientID4'...),
('clientID5'...),
('clientID6'...),
...
('clientID100'...);
  1. INSERT INTO clientaddresses(clientID,...)
  2. VALUES
  3. ('clientID1'...),
  4. ('clientID2'...),
  5. ('clientID3'...),
  6. ('clientID4'...),
  7. ('clientID5'...),
  8. ('clientID6'...),
  9. ...
  10. ('clientID100'...);


That should allow you to notice a considerable difference in how long this takes since you would be reducing the number of read/write operations on your database by a factor of 100. You could probably do this in 1000 sized or larger blocks as well depending on how much data is being transferred and how much memory you are able to use.
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

That's what I needed to know. I was thinking too small in terms of SQL. It's going to take me a bit to get that put together, but I will post here how it went.

Thanks. I've tried several different minor changes which all take time in their own right and none paid off so I appreciate the advice path that will give me a good shot, once complete, at speeding this thing up.
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9086
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Please keep us posted how it goes, I would be curious if you notice a big difference :)
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

I finished re-writing the script to build the extended queries and already ran the script. So far it looks like it took the transfer down from originally 2.5 hours to around 30 minutes. Yeah, quite a big difference. The individual writes were obviously the main reason for the slowness.

Thanks for your help. I think now I can work on little logics inside the loops that may help speed up the script even more and I'll be set for when I have to roll this thing out. We are swapping servers so this will reduce our downtime for sure (about 2 hrs).
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9086
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

No problem, glad I could help :)

Looks like its about 5x faster than before! Curious to how large of blocks you are doing at a time? Have you tried to adjust that any to see if it makes any further differences?
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

No, I kept it at 5000 records "LIMIT 0, 5000". In order to keep my WRITES down. All the tables are relational and the main table has a little over 40k records so at 5000 I should be writing 9 times. I felt like that was a good starting point.

I'm going to toggle the chunk size cause I've got to run it 3 or so more times anyway for testing. I'll post back my run times at different chunk sizes.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

I'm curious what sort of logic gets applied to the conversion from table to table. My first thought is to copy the table and use ALTER TABLE and maybe a few tricks that could utilize indexes instead of processing every row when applying the logic.
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Well, first off I just noticed joeberterator. Like it. I basically change some 1 or 0 to Yes or No. My database I like to use Y/N or Yes or No enumerated fields. The table I'm transferring from uses 1 or 0 for yes or no. So, I can't look at the db and always know what is what. Also, it's easier to use the data in my program without having to add that logic to every page that pulls the 1 or 0 to change it. Other than that, I may fix some dates but mostly its the bit field I change to enumerated.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

I'm thinking a copy of the table could be made in a couple of minutes tops, especially if they're MyISAM tables and one were to explore copying the files MySQL uses to store these tables as suggested in the first paragraph of this page. (then seconds rather than minutes)

Then, ALTER TABLE to add an ENUM column with a default value of NO. Then UPDATE to change that columns value to YES where your existing column is 1.
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

After looking at the tables I think I can do that for a few of the tables which will reduce the number of tables I actually have to rebuild. I'm just running out of time on this thing and I don't know if I have time to come up with an ALTER TABLE procedure to fix all the tables. The sync script runs in about 30 minutes and already handles everything because many of the tables differ quite a bit, but I think there are some tables that really did not change a great deal and can be transferred with about 3 SQL commands using that method. I'm going to work on it some today and see. Thanks for the help.

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: No registered users and 107 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.