Completed

Enhance VBSEO Likes System (PHP, MySQL) - repost

This project was successfully completed by developerpmal for $41 USD / hour in 6 days.

Get free quotes for a project like this
Employer working
Project Budget
$25 - $50 USD / hour
Completed In
6 days
Total Bids
2
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.*,[url removed, login to view] as to_userid, [url removed, login to view] as to_username, [url removed, login to view] as cmstitle, [url removed, login to view] as cmstext, [url removed, login to view] as cmsdateline, [url removed, login to view] , [url removed, login to view], [url removed, login to view] , [url removed, login to view], if([url removed, login to view], [url removed, login to view], [url removed, login to view]) AS posttitle, [url removed, login to view] as posttext, [url removed, login to view] AS postdateline, [url removed, login to view], [url removed, login to view] AS threadtitle, [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], NOT ISNULL([url removed, login to view]) AS hascustomavatar, [url removed, login to view] AS avatardateline, NOT ISNULL([url removed, login to view]) AS hascustom

FROM vb_vbseo_likes l LEFT JOIN vb_user u1 on [url removed, login to view] = l.l_from_userid

LEFT JOIN vb_user u2 on [url removed, login to view] = l.l_dest_userid

LEFT JOIN vb_cms_node node

on l_ctype = 3 AND [url removed, login to view] = l.l_cgroup AND node.setpublish=1 AND hidden=0

LEFT JOIN vb_cms_nodeinfo ni on [url removed, login to view] = [url removed, login to view]

LEFT JOIN vb_cms_article ca on [url removed, login to view] = [url removed, login to view]

LEFT JOIN vb_avatar AS avatar ON([url removed, login to view] = [url removed, login to view])

LEFT JOIN vb_customavatar AS customavatar ON([url removed, login to view] = [url removed, login to view])

LEFT JOIN vb_post p on l_ctype = 1 AND [url removed, login to view] = l_contentid

LEFT JOIN vb_thread t on [url removed, login to view] = [url removed, login to view]

WHERE (l_ctype 1 OR ([url removed, login to view] 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 (( ([url removed, login to view] IN (1,2,5,7,100,101,102,170)) OR ([url removed, login to view] =1) OR ( [url removed, login to view] in (1,2,5,7,100,101,102,170) AND ([url removed, login to view] IN (1,2,5,7,100,101,102,129,170) OR [url removed, login to view] = 1) AND

[url removed, login to view] > 0 AND [url removed, login to view] < 1369828338 ))))

ORDER BY l_dateline desc

LIMIT 0,10;

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

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online