Enhance VBSEO Likes System (PHP, MySQL) - repost

Avg Bid (USD)
$34 / hr
Project Budget (USD)
$25 - $50 / hr

Project Description:
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
LIMIT 0,10;

I will provide a complete test environment on an AWS server.

Hours of work: Unspecified Project Duration: < 1 week Skills required:
MySQL, PHP, vBulletin
About the employer:
Public Clarification Board
Bids are hidden by the project creator. Log in as the employer to view bids or to bid on this project.
You will not be able to bid on this project if you are not qualified in one of the job categories. To see your qualifications click here.

Hire developerpmal
$41 / hr
Hours: 6 hr/ week
Hire weworkforu
$27 / hr
Hours: 3 hr/ week