Slow queries

[tscbh]

I had this type of query which show on my slow-queries log. It always cost my server about 5~6 seconds.

Could you please tell me if this is normal. How do I optimize it?

I do not have any other queries as slow as that.

# Time: 080507 18:05:58
# User@Host: forum @ [192.168.0.1]
# Query_time: 5 Lock_time: 0 Rows_sent: 30 Rows_examined: 328846
use forum;
SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
,IF((NOT(options & 1024) AND (user.usergroupid IN (-1,6) OR FIND_IN_SET('6', membergroupids))), 0, reputation) AS reputationscore,level
,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), 1, 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 reputationlevel AS reputationlevel ON(user.reputationlevelid=reputationlevel.reputati onlevelid)
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,7,2,5)
ORDER BY reputationscore asc , user.username
LIMIT 61680, 30;

[ssslippy]

Do you have your avatars stored in the database?

[tscbh]

Yeah,

Thanks ssslippy. I move it to file storage now. I couldn't believe it's served from the database! I have more than 80,000 members. That's why my database server so slow.

[ssslippy]

Dont forget to move your attachments there also and user album pictures should also be stored to files instead of the database.

[Lynne]

And, don't forget that if they are stored in the filesystem, they do NOT get backed up when you backup your database. You must back those files/directories up separately now.

[tscbh]

Hi guys,

Thanks for all the tips.

I already moved all attachments (I don't have user album pictures), but yesterday I still got slow queries on my sql server.

I used a separate server for sql. This server is using only 5% of the CPU on average.

I don't know why I got that sql queries. Do I have to index anything to make it faster. What I see is mysql server examine 328,846 rows. This is not good isn't it?

Any suggestion?

[ssslippy]

Can you tell us what page and what query is causing this? I dont see why anything should be looking at 300k rows.

[tscbh]

I have no idea which page does it. My forum is pretty simple, very limited mod used.

I got about 20 of this queries in 1 days. This isn't much, but hackers can use this to attach my server.

After examining the slow queries log, I found that some queries examine as much as 420K rows.

Any idea?

[Lynne]

You need to go do a search through your pages to see where this query is being used. Do a global search for something like "ISNULL(customavatar.userid) AS hascustomavatar" since it looks like a pretty specific thing that doesn't get used often.

edit: Yep, only 22 instances. It looks very much like the query in memberlist.php on line 562. Start with that, see if it's the same query and if there are additions to the query from the standard (like from the hook above it - memberlist_fetch).

[ssslippy]

Would you happen to have living avatars installed?