Slow query - Likes Given - 3.6 PL2

Code:

# Time: 120829  6:45:48# User@Host: username[hostname] @ localhost []
# Query_time: 20.840613  Lock_time: 0.000340 Rows_sent: 15  Rows_examined: 1269086
SET timestamp=1346240748;
SELECT SQL_CALC_FOUND_ROWS l.*,u2.userid as to_userid, u2.username as to_username, b.title as blogtitle,b.firstblogtextid, bt.pagetext as blo
gtext, bt.dateline as blogdateline, u1.username , u1.userid, u1.usergroupid , p.postid, if(p.title, p.title, t.title) AS posttitle, p.pagetex
t 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 vb3_vbseo_likes l LEFT JOIN vb3_user u1 on u1.userid = l.l_from_userid
LEFT JOIN vb3_user u2 on u2.userid = l.l_dest_userid
LEFT JOIN vb3_blog b 
on l_ctype = 2 AND b.blogid = l.l_cgroup AND b.state='visible' 
LEFT JOIN vb3_blog_text bt
on l_ctype = 2 AND bt.blogtextid = l.l_contentid AND bt.state='visible' 
LEFT JOIN vb3_avatar AS avatar ON(avatar.avatarid = u1.avatarid) 
LEFT JOIN vb3_customavatar AS customavatar ON(customavatar.userid = u1.userid)
LEFT JOIN vb3_post p on l_ctype = 1 AND p.postid = l_contentid 
LEFT JOIN vb3_thread t on t.threadid = p.threadid
WHERE (l_ctype <> 1 OR (t.forumid in (140,3,30,7,153,23,130,131,156,44,124,96,136,101,100,48,70,102,66,144,148,149,145,146,147,98,75,73,36,78
,76,79,77,74,8,123,27,125,58,67,87,57,71,129,127,65,19,135,118,43,41,63,55,10,154,56,64,139,138,152,151,150,52,42,20,137,155,143,107,112,126,
113,115,116,114,46,88,119,85,121,133,89,86,9,11,92,128,12,95,93,37,45,90,103,106,108,105,47,6,111,21,109,54,122,59,134,141,142,80,83,82,117,1
32))) AND (l_from_userid= 3081) AND (l_ctype <> 2 OR (bt.blogtextid is not null))
ORDER BY l_dateline desc
LIMIT 0,15;

Any ideas how we can optimize this query? This user has ~19k likes given. We have seen a slow query with as little as 8,000 likes given. We have currently disabled the "likes given" link in vbseo_profile_menu template.

Any help would be appreciated. Thank you for your time.

Edit: VB 4.2 PL2

Oleg ?

Could you please open a ticket with ftp/admincp access for troubleshooting?

Update: those who have similar issues with like queries, please try running these queries to remove combined index for likes table and use individual indexes instead:

Code:

ALTER TABLE vbseo_likes DROP INDEX `l_contentid`;

ALTER TABLE vbseo_likes ADD INDEX (`l_contentid`);
ALTER TABLE vbseo_likes ADD INDEX (`l_ctype`);
ALTER TABLE vbseo_likes ADD INDEX (`l_cgroup`);
ALTER TABLE vbseo_likes ADD INDEX (`l_from_userid`);
ALTER TABLE vbseo_likes ADD INDEX (`l_dest_userid`);
ALTER TABLE vbseo_likes ADD INDEX (`l_dateline`);