You have chosen to sponsor your bid up to a maximum amount of .
VBSEO is a popular SEO plugin for VBulletin. It includes a feature called the Likes Tree, which allows users to like each others VBulletin forum posts. The likes show on the bottom of posts and in a listing page in a user's profile.
VBSEO have published code to all the most recent likes, and the most liked members to be shown in the VBulletin forum blocks system - normally a right column of extra content. However on a large forum the queries that create these are slow, and on my own forum bring it to a complete stop - the DB server falls over.
The job consists of three tasks to be delivered as complete modifications to the VBSEO 3.6.1 files:
- optimise the SQL queries for the most liked members block so that it runs in less than a second on my VBulletin DB
- same for the recent likes
- add a feature to change user secondary user group based on the number of likes received and/or a users likes to posts ratio, and to update this hourly using the VB scheduled tasks.
To give an idea of the SQL optimisation needed, this is the killer query of the most recent likes block:
SELECT SQL_CALC_FOUND_ROWS l.*,u2.userid as to_userid, u2.username as to_username, ni.title as cmstitle, ca.pagetext as cmstext, node.publishdate as cmsdateline, u1.username , u1.userid, u1.usergroupid , p.postid, if(p.title, p.title, t.title) AS posttitle, p.pagetext as posttext, p.dateline AS postdateline, t.threadid, t.title AS threadtitle, t.forumid, u1.avatarid, u1.avatarrevision, avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline, NOT ISNULL(customavatar.userid) AS hascustom
FROM vb_vbseo_likes l LEFT JOIN vb_user u1 on u1.userid = l.l_from_userid
LEFT JOIN vb_user u2 on u2.userid = l.l_dest_userid
LEFT JOIN vb_cms_node node
on l_ctype = 3 AND node.nodeid = l.l_cgroup AND node.setpublish=1 AND hidden=0
LEFT JOIN vb_cms_nodeinfo ni on ni.nodeid = node.nodeid
LEFT JOIN vb_cms_article ca on ca.contentid = node.contentid
LEFT JOIN vb_avatar AS avatar ON(avatar.avatarid = u1.avatarid)
LEFT JOIN vb_customavatar AS customavatar ON(customavatar.userid = u1.userid)
LEFT JOIN vb_post p on l_ctype = 1 AND p.postid = l_contentid
LEFT JOIN vb_thread t on t.threadid = p.threadid
WHERE (l_ctype 1 OR (t.forumid in (3,4,14,67,39,22,31,32,50,51,7,64,66,65,40,27,44,45,48,49,62,24,34,8,29,56,38,57,59,58,60,61,11,46,41,47,10,17,35,63,25,33,36,37,43,54,6,19,18,20,23,26,12,53,9,16,42,5,21,52,55,28))) AND (l_ctype 3 OR (( (node.permissionsfrom IN (1,2,5,7,100,101,102,170)) OR (node.userid =1) OR ( node.permissionsfrom in (1,2,5,7,100,101,102,170) AND (node.parentnode IN (1,2,5,7,100,101,102,129,170) OR node.nodeid = 1) AND
node.setpublish > 0 AND node.publishdate < 1369828338 ))))
ORDER BY l_dateline desc
I will provide a complete test environment on an AWS server.