mysql performance problem: inefficent query?

[mfyvie]

Hi guys,

I recently modified and ported someone else's mod to 3.6.x over on this post:

https://vborg.vbsupport.ru/showpost....17#post1199617

The original mod was to ensure to that all users get a unique avatar, and that they can't select the same avatar from the gallery. I changed the original query so that it provided the functionality I wanted (used avatars would simply disappear from the list).

In the last few months I've noticed periods where the board gets slow and I get reports via email of mysql exceeding the allowed number of connections.

I started logging the data and discovered that the same query was always present whenever there was a problem. This query seems to stay in the state "copying to tmp table" for some time. All other queries (like from other users going about their business on the forum) seem to be in a locked state until this original query clears.

When the system isn't busy, this query obviously clears in a reasonable period of time, and nobody notices. However, when the system is busy this query can take over a minute, and as the additional queries stack up, the number of connections soon runs out.

Unfortunately I don't know very much about mysql, I just kind of hacked this query together through trial and error, but this is what it looks like:

SELECT avatar.*, imagecategory.title AS category, user.avatarid AS useravatarid
FROM avatar AS avatar LEFT JOIN imagecategory AS imagecategory USING(imagecategoryid) LEFT JOIN user AS user ON (user.avatarid=avatar.avatarid)
WHERE minimumposts <= 2675
AND avatar.imagecategoryid=16
AND avatar.avatarid <> 0
AND ISNULL(user.avatarid)
ORDER BY avatar.displayorder
LIMIT 0,100

This was the exact query that I got from the "show full processlist" in mysql. It executed when I went into the control panel to select an avatar from the gallery.

Remember this query is not the vbulletin default!

So I have two questions:

1. Why do all other mysql queries get assigned a locked state before this one finishes?
2. Is there something about my query which is causing a massive load on the server? Surely it should complete in less than 1-2 minutes? Is there a more efficient way to write this query?

Thanks in advance for any assistance.

Just an update to this issue. I managed to solve the problem myself by adding an index to users.avatarid. I updated the original thread (referenced at the top of this thread) with the issue and the solution.