[SOLVED] Extreme Looping

[Cless]

This is probably going to sound odd, but anyway:

 

I need to loop through almost 3 million+ entries in an MySQL database. Obviously using the while statement for this is not ideal.

 

What would be the best way to do this? Cron jobs? Or is there some sort of premade script that would allow for me to do efficiently do this?

 

What I have to do is loop through these entries, change some things, then essentially duplicate the entry to a different database on my server (not an exact copy: it converts many things before doing this), and do some other checks.

 

For the record, I have a 3.0 GHz quad server, 8 GB of ram.

[PFMaBiSmAd]

If you don't need to do this actively on live data (data that is being created or changed while you are performing the operation), it would be to your advantage to make a backup of the original database and do this on an off-line system. Any accidents won't affect the live data and there won't be any performance issues on the live server while you are doing this.

 

If you are making the exact same from/to change in all or many of the records, you can do this with a single UPDATE query (no WHERE clause or one that matches many records at one time.) No looping is required.

 

If you are globally changing something like a domain name in links or server specific file paths in data, it would also be to your advantage to NOT store any domain or server specific information as part of the data. Either use relative links/paths or keep the domain/path in configuration variables and prepend it after the generic data has been retrieved to form absolute links and paths.

 

You would need to be more specific about what you are changing to get more specific suggestions.

[Cless]

Well, it's not an exact copy: it needs to select the data, convert it (divide some fields by 2,  etc.) then insert the converted data into a different database.

 

For example:

 

<?php

$result= mysql_query("SELECT * FROM table");

while($rows= mysql_fetch_array($result))
{
$number1= $rows['number1'];
$number2= $rows['number2'];
$number3= $rows['number3'];

$number1= $number1 / 2;
$number2= $number2 + 5;
$number3= $number3 * 10;

$result2= mysql_query("SELECT * FROM table2 WHERE number='$number1' LIMIT 1");
$rows2= mysql_fetch_array($result2);

if(empty($rows2))
{
$number1= $number1 + 5;
}

//change database here

mysql_query("INSERT INTO table_thats_in_the_other_database (number1, number2, number3)
VALUES ('$number1', '$number2', '$number3')");
}

?>

 

So, basically, I have to loop through a table with about 2.7 million entries, do some conversions, check for certain thing using MySQL, then insert the final results in the new database.

[Philip]

Well, for your example this one query would do the trick:

INSERT INTO otherTable SELECT (num1/2) as num1, (num2+5) as num2, (num3*10) as num3 FROM firstTable

 

num1	num2	num3
56	23	54
123	486	123
48	34	123
6456	321	6
65	1	23
564	123	123
46	89	654
456	231	564
489	65	2389
8956	56	23

Then after the query, the second table:

num1	num2	num3
28	28	540
62	491	1230
24	39	1230
3228	326	60
33	6	230
282	128	1230
23	94	6540
228	236	5640
245	70	23890
4478	61	230

[Cless]

That's not what I'm actually trying to do (was a basic example). There are some MySQL checks during the loop, and some other inserts in that loop.

[corbin]

Depending on the complexity of the data processing, you may be stuck doing it the current way.

 

 

 

Your only option might be to keep as much as you can in SQL statements and just optimize the crap out of your PHP code.

[Cless]

Maybe.

 

Would cron jobs be sufficient for something like this?

[Philip]

Without specifics of what you'd like to do to your data, it is hard to give the best solution for your needs.

 

If you do keep looping it the way you are, you need to make sure to give your server some breathing time every couple of thousand rows.

[Cless]

Yeah, would cron be good for doing that?

 

Or even, the use of JavaScript setInterval and AJAX.

[Philip]

A cron would work, maybe not the best solution but it would work.

[Cless]

Would you know what command I would use to call the file? I can use cPanel's cron feature to do it, but I haven't use cron much, so I'm not quite sure what command to use.

[Philip]

You'd need to create a custom PHP script that the cron runs. This would need to keep track of where the last cron job left off (thus where it should start) and then contain the loop. I'd just setup the cron via cPanel.

[Cless]

Yeah, that's what I was planning.

 

However, in the cPanel, I'm not exactly sure what to type in as the command to run.

[Philip]

http://www.webmasterworld.com/forum88/7299.htm

[Cless]

Thank you. I will try it out and post my results later on.

[Cless]

There. Apparently cron wasn't the answer. I just ran my query (but I used set_time_limit(0) and turned my site offline) and it ran perfectly, surprisingly. Didn't even lag.

[Philip]

Good to hear you got it to work. Next time, just be sure to post more details and we can help you get to your conclusion faster and more efficiently.