Large Database Manipulation

[iversonm]

Hey peoples

 

I need some advice,

 

I am working on converting a forum for someone as a favor. It is a forum with 70,000 topics and almost 1kk posts. I have already figured out how to convert the users to phpbb.

 

Also forum data is extremely dirty. The guy who wrote the software didn't put in any validation. This forum has been going down lately for no reason and I am sure its due to issues with database info / structure.

 

<?PHP
$sql = 'SELECT forum_id, forum_name FROM Rabbitry.phpbb_forums WHERE parent_id != 0';
$query = MySQL::query($sql);

while($row = mysqli_fetch_array($query)){
$forums_id[$row['forum_id']] = $row['forum_name'];
$forum_name[$row['forum_name']] = $row['forum_id'];

}

$sql = 'SELECT topic_id, forum_id, poll_id, topic_name, topic_description, topic_views, topic_starter_id, topic_date_time FROM ultra.wowbb_topics';
$result = MySQL::query($sql);
while($row = mysqli_fetch_array($result)){
foreach($row as $key=>$value){
$row[$key] = MySQL::escape($value);
}
$row['topic_date_time'] = strtotime($row['topic_date_time']);
if($row['topic_date_time'] < 0){
$row['topic_date_time'] = NULL;
}

if($row['poll_id'] !== 0){
//get poll info

}
$sql_posts = 'SELECT wowbb_posts.post_id, post_text, attachment_id, user_id, post_date_time, post_ip, post_last_edited_on, post_last_edited_by FROM ultra.wowbb_posts INNER JOIN ultra.wowbb_post_texts USING (post_id) WHERE topic_id = \''.$row['topic_id'].'\'';
MySQL::query($sql_posts);
?>

 

 

I ran this, and it doesn't even have any insert statements and I let it go for 5 -6 minutes and it was still running.

 

Any recommendations would be really appreciated !!

I have never done anything this large from a conversion process.

 

Matt

[kicken]

You'll have to figure out which query is slow to run and try to speed it up, most likely through adding indexes. Take each of those queries and put EXPLAIN in front of them, then run them in a tool like mysql console or phpMyAdmin. Mysql will tell you how it is executing the queries, what indexes (if any) it is using and some other information about the query. You can use that information to decide how to speed up the queries.

 

If you need help interpreting the results of the EXPLAIN post both the query and the results here.

[iversonm]

From the standpoint of coding.

 

Pulling topics then posts for each topics then inserting, is that the best way to program that?

[Christian F.]

As long as you're not running any queries inside a loop, you're more or less golden. In any case it's not something you should worry about now.

First add indexes where necessary, as described by kicken. Then, if it's still slow, section the code into functions and run it through a profiler. It'll tell you exactly what takes how much time, and thus where you need to focus your optimization efforts.

[shlumph]

If the storage engine supports transactions, I would recommend starting one at the beginning of your script, committing at the end and rolling back if there's any errors. This should make the script perform faster and gracefully make sure that all or nothing goes through.

[iversonm]

Christian, there will be an insert within the loop

[Christian F.]

Don't do in a loop, or at least not in every single iteration. Rather use the loop to build up the query, within the max size the MySQL client library accepts, and the run it. It'll save you a whole lot of time and effort. Alternatively, you could use the above script to write a SQL file, and then manually import it afterwards. Not sure which is the most efficient method.

[kicken]

I personally would have the code generate a SQL script (or several) which will let you do a quick review of the data before trying to import it to ensure things are correct.

 

Depending on how much data translation you need to do in order to convert from one board to the other you might be able to get away w/ just using a INSERT INTO ... SELECT query and not have to mess with PHP at all.