Slow queries

[tscbh]

First of all, I don't know what is living avatar. I am not a forum type person. I only use forum general discussion and user database.

I found out that slow queries only appear when I browse the member list. The first few page of the member list is OK, but as soon as I reach page 2.5K+ slow queries appear. So how can I fix this problem.

On the memberlist, the following detail is shown:
- username/posts/reputaion/avatar

Most of my queries are very simple and index probably. It's usually took less than 50ms. Above that would worry me.

5 seconds for a single query is way too much.

Is there anything I can do to reduce this to an accepted level?

tscbh

[ssslippy]

Are you displaying avatars on your memberlist page. If im correct these pages arnt in your cache.

[tscbh]

Yes avatar is displayed on the memberlist. I don't know if it's cached. How do I cached the memberlist?

[ssslippy]

no no, usually the system drops files that arnt access often out of the cache. Nothing you can do about that, my best advice is to disable avatars in the memberlist.

[tscbh]

After removing everything from member list, nothing seemed to change.

I add a condition in member list, only show member > x post count. Then I index post count. I have only about 60 pages left. The longest queries which is the last page is 0.2 seconds. I am feeling much better now. The majority of member is remove from the list .

There is nothing I can do about it.

vBulletin.org suffer the same problem too:

 Showing results 17576 to 17600 of 17601             
            Search took 3.01 seconds.

If there are 80,000+ members, the result should be worse.

Anyway, thanks for all the support guys.

[mainframe]

Same problem here with 471.000 members...
Sometimes the query takes 9 seconds and I also found that the only slow queries generated by vBulletin are in the memberlist..

[mindfrost82]

OMG, this thread saved me. I've been trying to troubleshoot an issue for over a month now and I never thought it was MySQL, much less this issue with avatars on the memberlist.

We have almost 400,000 members and we had avatars showing on the memberlist.

So, I disabled avatars on that page, and also made it so that only members with >1 post show up and we haven't had a slow query since then.