mysql performance problem: inefficent query?

[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.