Fixed vBulletin 4.x Import: 5x performance improvement for user import

[Steffen]

Affected version

2.0.0

Using MySQL (Percona Server) 5.6.37 I've been able to speed-up the user import by a factor of 5-6 with a small patch.

Before:

+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------+------+----------------------------------------------------+
| id | select_type | table         | type   | possible_keys | key     | key_len | ref                           | rows | Extra                                              |
+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------+------+----------------------------------------------------+
|  1 | SIMPLE      | user          | range  | PRIMARY       | PRIMARY | 4       | NULL                          |  355 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | userfield     | eq_ref | PRIMARY       | PRIMARY | 4       | computerbase.user.userid      |    1 | NULL                                               |
|  1 | SIMPLE      | usertextfield | eq_ref | PRIMARY       | PRIMARY | 4       | computerbase.user.userid      |    1 | NULL                                               |
|  1 | SIMPLE      | admin         | ALL    | PRIMARY       | NULL    | NULL    | NULL                          |    3 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | userban       | eq_ref | PRIMARY       | PRIMARY | 4       | computerbase.user.userid      |    1 | NULL                                               |
|  1 | SIMPLE      | usergroup     | eq_ref | PRIMARY       | PRIMARY | 2       | computerbase.user.usergroupid |    1 | NULL                                               |
|  1 | SIMPLE      | customavatar  | eq_ref | PRIMARY       | PRIMARY | 4       | computerbase.user.userid      |    1 | Using index                                        |
+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------+------+----------------------------------------------------+

After:

+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------+------+-------------+
| id | select_type | table         | type   | possible_keys | key     | key_len | ref                           | rows | Extra       |
+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------+------+-------------+
|  1 | SIMPLE      | user          | range  | PRIMARY       | PRIMARY | 4       | NULL                          |  355 | Using where |
|  1 | SIMPLE      | userfield     | eq_ref | PRIMARY       | PRIMARY | 4       | computerbase.user.userid      |    1 | NULL        |
|  1 | SIMPLE      | usertextfield | eq_ref | PRIMARY       | PRIMARY | 4       | computerbase.user.userid      |    1 | NULL        |
|  1 | SIMPLE      | admin         | eq_ref | PRIMARY       | PRIMARY | 4       | computerbase.user.userid      |    1 | NULL        |
|  1 | SIMPLE      | userban       | eq_ref | PRIMARY       | PRIMARY | 4       | computerbase.user.userid      |    1 | NULL        |
|  1 | SIMPLE      | usergroup     | eq_ref | PRIMARY       | PRIMARY | 2       | computerbase.user.usergroupid |    1 | NULL        |
|  1 | SIMPLE      | customavatar  | eq_ref | PRIMARY       | PRIMARY | 4       | computerbase.user.userid      |    1 | Using index |
+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------+------+-------------+

diff --git a/xenforo/src/XF/Import/Importer/vBulletin.php b/htdocs/xenforo/src/XF/Import/Importer/vBulletin.php
--- a/xenforo/src/XF/Import/Importer/vBulletin.php
+++ b/xenforo/src/XF/Import/Importer/vBulletin.php
@@ -797,7 +797,7 @@ class vBulletin extends AbstractForumImporter
             STRAIGHT_JOIN {$this->prefix}usertextfield AS
                 usertextfield ON (user.userid = usertextfield.userid)
             LEFT JOIN {$this->prefix}administrator AS
-                admin ON (user.userid = admin.userid)
+                admin FORCE INDEX (PRIMARY) ON (user.userid = admin.userid)
             LEFT JOIN {$this->prefix}userban AS
                 userban ON (user.userid = userban.userid)
             LEFT JOIN {$this->prefix}usergroup AS

 

[Kier]

Thanks for the suggestion, I'll give it a try