Help with this slow queries!!

[jorge_kai]

Hi friends... i've a big problem with one of my forums... I do not know what causes this but I have queries that need to be reviewed and a half million rows! I do not know what could be the reason, but are queries that take longer than 10 seconds to complete and make my forum has become very slow. Could anyone advise? i have vbulletin 3.8.1, thanks

the queries

# Time: 110110 14:14:27
# User@Host: htcmania[htcmania] @ localhost []
# Query_time: 10.445781 Lock_time: 0.000155 Rows_sent: 30 Rows_examined: 1565698
use htcmania;
SET timestamp=1294665267;
SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid

,avatar.avatarpath,NOT ISNULL(customavatar.userid) AS hascustomavatar,customavatar.dateline AS avatardateline, customavatar.width AS avwidth, customavatar.height AS avheight

, IF((options & 512 AND user.userid <> 0), 0, lastactivity) AS lastvisittime



FROM user AS user
LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
LEFT JOIN userfield AS userfield ON(userfield.userid=user.userid)

LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)



WHERE 1=1
AND (user.usergroupid IN (-1,6,11,10,12,15,16,14,7,9,5,2,13))

ORDER BY user.username asc
LIMIT 385410, 30;
# Time: 110110 14:16:46
# User@Host: htcmania[htcmania] @ localhost []
# Query_time: 10.659095 Lock_time: 0.000176 Rows_sent: 30 Rows_examined: 1572993
SET timestamp=1294665406;
SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid

,avatar.avatarpath,NOT ISNULL(customavatar.userid) AS hascustomavatar,customavatar.dateline AS avatardateline, customavatar.width AS avwidth, customavatar.height AS avheight

, IF((options & 512 AND user.userid <> 0), 0, lastactivity) AS lastvisittime



FROM user AS user
LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
LEFT JOIN userfield AS userfield ON(userfield.userid=user.userid)

LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)



WHERE 1=1
AND (user.usergroupid IN (-1,6,11,10,12,15,16,14,7,9,5,2,13))

ORDER BY user.username asc
LIMIT 387810, 30;