UBB.threads PHP Forum Software Community Forums UBB.threads 7 Importer Support Importer performance tweak

I'm importing a few tens of thousands of topics with something like 1.3 million posts into ubb7, and it gets really slow as I get upwards of 30k topics. It looks to me like this is because sorting on b_number and using a LIMIT n, 21 to get the next block of 20 topics requires MySQL to filter and sort the entire list of n-1 topics to get to the nth one.

Wouldn't it be faster to pass the last-processed topic number instead of ordinal, and change the query to something like:

select * from ${PREFIX}Posts WHERE B_Topic = 1 AND B_Number > [the last one processed] ORDER BY B_Number ASC LIMIT 20

That runs very much faster on my test system.

Thanks,

-D

That very well may be a better route to go. I'll work on making that change as soon as I get a chance.

Hi Rick,

Here's the patch I applied to the importer script. It hasn't seen any real testing yet, but I figured it might be a good start. I also added an index on w3t_Posts (B_Topic, B_Number) which lets MySQL scan far fewer index rows to find topics, and in fact gets faster the closer it gets to the end.

Hope this proves useful.

-D

62a63,64
> $startPostID = get_input("startPostID","get","int");
> if(strlen($startPostID) < 1) { $startPostID = 0;}
771c773
< global $html, $dbh, $cgi, $dbh65, $dbprefix, $start, $config, $topics, $site_id, $graemlins;
---
> global $html, $dbh, $cgi, $dbh65, $dbprefix, $start, $config, $topics, $site_id, $graemlins, $startPostID;
788c790
< $limit = "$start,21";
---
> $limit = "21";
792a795
> AND B_Number > $startPostID
795a799
> //echo "<pre> $query</pre><br/><br/>\n";
827a832,834
>
> // remember this topic's ID
> $endID = $topic_data['B_Number'];
1083c1090
< transition("Continuing...", "threads_import.php?ubb=posts&start=$end", 1);
---
> transition("Continuing...", "threads_import.php?ubb=posts&start=$end&startPostID=$endID", 1);