XF 2.2 Performance issue post vBulletin import

[RustedBucket]

So I have an interesting problem after importing a VB 4.2.5 forum into XF 2.2.3. When I try to load forum.php the browser just spins for close to 14 minutes before rendering the page. I've tried everything I can think of but can't settle on an error anywhere. No database errors, no Apache/PHP errors, nothing with debug=true. This is a test site so it's not critical, Macbook Pro, 4 I-7s, 16Gb so it's got plenty of power to handle just me. Incidentally the site runs fine on the same system in VB.

All I've done is ran the import. The XF system was setup as a fresh install that worked fine prior to the import (empty). The admin side loads fine.. Ancillary pages load fine as do post pages. This seems isolated to the forum/sub-forum pages.

I'm wondering if it might be the counters but that's all I can think of. Also, nothing in the slow query logs.

Any ideas?

 

[RustedBucket]

So I've track my problem down to this query. The problem seems to be caused from remnant elements after mess purging users with 0 posts from VB via the VB purge system. Not everything got removed causing a bunch of user_id to be set to 0 in xf_profile_post.user_id. This causes the second LEFT JOIN to do a huge number of table scans. Changing them to JOINS or adding a xf_profile_post.user_id != 0 to the ON condition of the second LEFT JOIN seems to fix it. At least for this query. Removing the IDs however did not so I'm guessing there are other tables with the same problem... If anyone runs into something like this, check your tables for left over artifacts.

Anyhow, anybody have an idea where this query is built in the system, I'm having trouble finding it. Thanks

SELECT xf_profile_post.*, xf_user_ProfileUser_1.*, xf_user_privacy_Privacy_2.*, xf_user_User_3.*
FROM xf_profile_post
         LEFT JOIN xf_user AS xf_user_ProfileUser_1
                   ON (xf_user_ProfileUser_1.user_id = xf_profile_post.profile_user_id)
         LEFT JOIN xf_user_privacy AS xf_user_privacy_Privacy_2
                   ON (xf_user_privacy_Privacy_2.user_id = xf_user_ProfileUser_1.user_id)
         LEFT JOIN xf_user AS xf_user_User_3 ON (xf_user_User_3.user_id = xf_profile_post.user_id)
WHERE (xf_profile_post.message_state = 'visible')
ORDER BY xf_profile_post.post_date DESC