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.

[Eikinskjaldi]

Originally Posted by mfyvie

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.

1) Whether or not a query locks the system depends on which indices it is using, which version of mysql you are running, and how long the query takes to run.

2) When writing a new query, always get an explain first. You can do this from the command line or phpmyadmin or similar, just put the word "explain" in front of the select. It will spit out a table showing you how much work is involved in the query. Details of the output can be found here
http://dev.mysql.com/doc/refman/5.0/en/explain.html

If adding an index didn't help with the speed, and you have system access to your db, you can also play around with the tmp table parameters.