Help with some messy Vanilla MySql Queries (Mysql gurus welcome!)

Andy K

Hey all, recently my provider has been having some general latency issues. However, it looks like there are some genuinely bizarre requests being run against mysql, so I wanted to throw this past the mysql gurus in the house to see if this represents something that:

1) A user is running, where I can tell him "Stop!"
2) A piece of Vanilla MySql handling that just needs to be tightened up in a future release
3) Something that I can resolve.

...so that I can separate my vanilla issues from general server issues.

Here are the two queries:
==============
#1
=======
# User@Host: xxx @ xxx.xxx.com

# Query_time: 2 Lock_time: 0 Rows_sent: 50 Rows_examined: 84593

select t.DiscussionID as DiscussionID, t.FirstCommentID as
FirstCommentID, t.AuthUserID as AuthUserID, t.WhisperUserID as
WhisperUserID, t.Active as Active, t.Closed as Closed, t.Sticky as
Sticky, t.Sink as Sink, t.Name as Name, t.DateCreated as DateCreated,
t.LastUserID as LastUserID, t.DateLastActive as DateLastActive,
t.CountComments as CountComments, t.CategoryID as CategoryID, u.Name as
AuthUsername, lu.Name as LastUsername, wt.Name as WhisperUsername,
tuwf.LastUserID as WhisperFromLastUserID, tuwf.DateLastActive as
WhisperFromDateLastActive, tuwf.CountWhispers as CountWhispersFrom, tuwt.LastUserID as
WhisperToLastUserID, tuwt.DateLastActive as WhisperToDateLastActive,
tuwt.CountWhispers as CountWhispersTo, wluf.Name as
WhisperFromLastUsername, wlut.Name as WhisperToLastUsername, c.Name as
Category, b.DiscussionID is not null as Bookmarked, utw.LastViewed as
LastViewed, coalesce(utw.CountComments, 0) as LastViewCountComments,
fc.FormatType as FormatType, fc.Body as Body from LUM_Discussion t left
join LUM_User u on t.AuthUserID = u.UserID left join LUM_User lu on
t.LastUserID = lu.UserID left join LUM_User wt on t.WhisperUserID =
wt.UserID left join LUM_DiscussionUserWhisperFrom tuwf on t.DiscussionID
= tuwf.DiscussionID and tuwf.WhisperFromUserID = 26 left join LUM_User
wluf on tuwf.LastUserID = wluf.UserID left join
LUM_DiscussionUserWhisperTo tuwt on t.DiscussionID = tuwt.DiscussionID
and tuwt.WhisperToUserID = 26 left join LUM_User wlut on tuwt.LastUserID
= wlut.UserID left join LUM_Category c on t.CategoryID = c.CategoryID
left join LUM_CategoryRoleBlock crb on t.CategoryID = crb.CategoryID and
crb.RoleID = 3 left join LUM_UserBookmark b on t.DiscussionID =
b.DiscussionID and b.UserID = 26 left join LUM_UserDiscussionWatch utw
on t.DiscussionID = utw.DiscussionID and utw.UserID = 26 left join
LUM_Comment fc on t.FirstCommentID = fc.CommentID left join
LUM_CategoryBlock cb on t.CategoryID = cb.CategoryID and cb.UserID = 26
where (crb.Blocked = '0' or crb.Blocked = 0 or crb.Blocked is null ) and
t.Active = '1' and (cb.Blocked = '0' or cb.Blocked = 0 or cb.Blocked is
null ) and (t.WhisperUserID = '26' or t.WhisperUserID = 26 or
t.AuthUserID = '26' or t.AuthUserID = 26 or t.WhisperUserID is null or
t.WhisperUserID = 0 or t.WhisperUserID = '0' ) group by t.DiscussionID
order by t.Sticky asc, greatest(case t.WhisperToLastUserID WHEN 26 THEN
t.DateLastWhisper ELSE 0 END, case t.WhisperFromLastUserID WHEN 26 THEN
t.DateLastWhisper ELSE 0 END, t.DateLastActive) desc limit 0, 50;
===============
(I see userid 26 referenced in there: Any idea what kind of query/search is running here?)

and

==============
#2
=======
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 70341

select count(t.DiscussionID) as Count from LUM_Discussion t left join
LUM_UserBookmark b on t.DiscussionID = b.DiscussionID and b.UserID = 26
left join LUM_CategoryRoleBlock crb on t.CategoryID = crb.CategoryID and
crb.RoleID = 3 left join LUM_CategoryBlock cb on t.CategoryID = cb.CategoryID and cb.UserID = 26 where (crb.Blocked = '0' or crb.Blocked = 0
or crb.Blocked is null) and t.Active = '1' and (cb.Blocked = '0' or
cb.Blocked = 0 or cb.Blocked is null ) and (t.WhisperUserID = '26' or
t.WhisperUserID = 26 or t.AuthUserID = '26' or t.AuthUserID = 26 or
t.WhisperUserID is null or t.WhisperUserID = 0 or t.WhisperUserID = '0'
);
===============

Thoughts on what is running the above query? (What kind of search, etc) And perhaps how to tighten this up?

Thanks!
-Andy

WallPhone

If you navigate to example.com/your-vanilla/debug.php, you can turn on debug mode. Then any role with 'can view debug info' permission will be able to see the queries Vanilla generates at the bottom of the page. The first one appears to be a normal query that generates the discussion grid. (also note that it limits to 50 rows--my guess this is the same as the number of rows you have set per page) The second one also appears on a discussion grid page load, I believe it is used to determine how many pages of discussions there are. (passing thought--maybe both peices of information could be returned via the same query?) Figuring a way to optimize these queries may be quite a bit of work. Turning off whispers may help immensly--or it may not do anything at all. Another thought is to only check for whispers in a front page load if the user has sent/recieved a whisper recently enough to be on the first page.

lech

At a glance, that first query appears to be a fairly intensive search return. It could be pulling whispers or participated discussions or a combination of the two based on all the rows it's asking for. If you have saved searches enabled on the system check for a saved search belonging to user ID 26 or something similar like checking user activity based on role or generally everyone. There's also a few strange prefixes in there unless those are your table/db names, if not they're good identifiers to narrow down where to look.

Mark

26 is the user viewing the page. It needs to reference that so that whispers can appear for that user where applicable. I used to do just one query that would tell how many pages there are, but this was faster - esp when you get hundreds of thousands of discussions. If you want to speed it up and remove that wacky stuff, disable whispers.