In Progress

Enhance VBSEO Likes System (PHP, MySQL) - repost

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.

Skills: MySQL, PHP, vBulletin

See more: tree update, tree query, the p.i.c. group, node job, 50 likes and i will, node mysql, aws sql server, sql l, php aws, optimisation of php, AWS server, aws php, create update profile page php, vbulletin php mysql, mysql number sql, aws environment, node stop, order number mysql, php likes, dateline, mysql slow query update, create user profile page system php, forum slow, slow queries, query sql tree

About the Employer:
( 17 reviews ) Germany

Project ID: #4580308

Awarded to:

developerpmal

Please check PM.

$41 USD / hour
(107 Reviews)
7.2

1 freelancer is bidding on average $41 for this job

weworkforu

Hello, I hope you are having a good day. Your project sounds very interesting, and immediately I would say that it is something I can help you with. I am completely clear with the requirement and very much interested t More

$27 USD / hour
(0 Reviews)
0.0