Visitor Messaging query takes AGES to complete...

[DragonBlade]

I have a pair of users on my forum that have evidently never heard of IM clients. The converse.php between them is well over 4,000 pages long (estimating that half belong to each of 'em, that's over 20,000 messages a piece at 10 messages per page!).

Anyways, the query now takes AGES to execute--anywhere from 8 seconds (MINIMUM) on up to 20, and sometimes a whole hell of a lot more. I'm fairly sure this ain't my MySQL server settings, either.


The query in question...

(
SELECT
	visitormessage.*, visitormessage.dateline AS pmdateline, user.*, visitormessage.ipaddress AS messageipaddress, visitormessage.userid AS profileuserid
	,deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason
	,avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight, customavatar.width_thumb AS avwidth_thumb, customavatar.height_thumb AS avheight_thumb, filedata_thumb, NOT ISNULL(customavatar.userid) AS hascustom
	
FROM visitormessage AS visitormessage
LEFT JOIN user AS user ON (visitormessage.postuserid = user.userid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)
LEFT JOIN deletionlog AS deletionlog ON (visitormessage.vmid = deletionlog.primaryid AND deletionlog.type = 'visitormessage')

WHERE visitormessage.userid = 50976 AND visitormessage.postuserid = 35301 AND visitormessage.state IN ('visible','moderation','deleted')

	)
	UNION
	(
SELECT
	visitormessage.*, visitormessage.dateline AS pmdateline, user.*, visitormessage.ipaddress AS messageipaddress, visitormessage.userid AS profileuserid
	,deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason
	,avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight, customavatar.width_thumb AS avwidth_thumb, customavatar.height_thumb AS avheight_thumb, filedata_thumb, NOT ISNULL(customavatar.userid) AS hascustom
	
FROM visitormessage AS visitormessage
LEFT JOIN user AS user ON (visitormessage.postuserid = user.userid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)
LEFT JOIN deletionlog AS deletionlog ON (visitormessage.vmid = deletionlog.primaryid AND deletionlog.type = 'visitormessage')

WHERE visitormessage.userid = 35301 AND visitormessage.postuserid = 50976 AND visitormessage.state IN ('visible','moderation','deleted')

	)
	ORDER BY pmdateline DESC
	LIMIT 0, 10

Now, I'm guessing it only takes this long because the LIMIT cannot be included on the INSIDE of the individual queries (for the reason that it would screw up page numbers and whatnot, I suppose, I dunno fer sure).

So my question is, what's your advice? Should I just go in and delete several thousand of their VMs? Is there a pruning tool for VMs that I can use? Should I go rant uselessly about this at vB.com? XD



I don't want to disable VMs altogether, but I don't want members to have huuuuuge load times when loading these profiles, nor do I want an 8-20-second query locking up my server.