Slowest query in vBulleting and performance bottlenecks
 

We hired Certified MySQL DBA from the reputed company to look at our database to see what are the bottlenecks, what the slowest queries are and what can be done to improve the performance.

They suggested adding following indexes:
alter table vb_session add index idhash_user (idhash, userid);
alter table vb_search add index ipaddress_date (ipaddress, dateline);
alter table vb_user add index email (email(10));
alter table vb_session add index lastactivity_idx (lastactivity);
alter table vb_cpsession add index dateline_idx (dateline);
alter table vb_search add index dateline_idx (dateline);
alter table vb_useractivation add index dateline_idx (dateline);
alter table vb_threadredirect add index expires_idx (expires);

Also,
Currently the slowest query that you have is

SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid

,avatar.avatarpath,NOT ISNULL(customavatar.userid) AS hascustomavatar,customavatar.dateline AS avatardateline, customavatar.width AS avwidth, customavatar.height AS avheight

, IF((options & 512 AND user.userid 0), 0, lastactivity) AS lastvisittime



FROM vb_user AS user
LEFT JOIN vb_usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
LEFT JOIN vb_userfield AS userfield ON(userfield.userid=user.userid)

LEFT JOIN vb_avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN vb_customavatar AS customavatar ON(customavatar.userid = user.userid)



WHERE 1=1
AND (user.usergroupid IN (-1,6,30,38,39,31,34,26,37,29,7,2,5))

ORDER BY user.username asc
LIMIT 27330, 30;



Any comments?