[RESOLVED] MySQL: turning subqueries into joins?

I have the following query -- I'd like to think that this query and others like it are inefficient, and that perhaps there is a better way to do this with multiple joins. But, I've really only had queries with single joins before and I'm having a hard time wrapping my head around how I might better optimize this query.

Please bear with me if my query is ugly! I would absolutely appreciate any help that anyone is willing to give. The following is for a very simple forum, and is for displaying the threads contained within the specified forum. The subqueries are to get "other" information, like the number of posts in the thread, the last read post in this thread by this particular user, the last post's ID/date/author and that poster's usergroup. And then, I have a left join on the aid (author's ID) to grab the author's name, email, group, etc. Rather than trying to format the query myself, this is how phpMyAdmin shows it!

Code:

---

SELECT th.id, th.title, u.name, u.group, u.email, th.date, (

SELECT COUNT( p.id )
FROM forum_posts p
WHERE p.tid = th.id
) AS posts, (

SELECT r.lastpostread
FROM forum_threads_read r
WHERE r.uid =1
AND r.tid = th.id
) AS lastpostread, (

SELECT po.id
FROM forum_posts po
WHERE po.tid = th.id
ORDER BY po.id DESC
LIMIT 1
) AS lastpostid, (

SELECT po.date
FROM forum_posts po
WHERE po.tid = th.id
ORDER BY po.id DESC
LIMIT 1
) AS lastpostdate, (

SELECT us.name
FROM forum_posts po
LEFT JOIN users us ON po.aid = us.id
WHERE po.tid = th.id
ORDER BY po.id DESC
LIMIT 1
) AS lastpostuser, (

SELECT us.group
FROM forum_posts po
LEFT JOIN users us ON po.aid = us.id
WHERE po.tid = th.id
ORDER BY po.id DESC
LIMIT 1
) AS lastpostusergroup

FROM forum_threads th
LEFT JOIN users u ON th.aid = u.id
WHERE th.fid =2
ORDER BY th.sticky DESC , th.date DESC

---

The thing I'm particularly worried about, for example, is the fact that I have at least three different subqueries all referencing forum_posts, but I can't return multiple values with a subquery. Could all of that be done a simpler way?

edit: oh, and I'm not even sure if I should be posting sample copies of my tables, or if this query is sufficient enough.

Due to the way database systems optimise queries (such as re-using the subqueries, even with different fields), I doubt that this will make much difference to the speed, but it is at least shorter and easier to read:

Code:

---

SELECT th.id, th.title, u.name, u.group, u.email, th.date, (

SELECT COUNT( p.id )
FROM forum_posts p
WHERE p.tid = th.id
) AS posts, (

SELECT r.lastpostread
FROM forum_threads_read r
WHERE r.uid =1
AND r.tid = th.id
) AS lastpostread,

LastPost.ID AS lastpostid,
LastPost.date AS lastpostdate,
LastPostUser.name AS lastpostuser,
LastPostUser.group AS lastpostusergroup

FROM forum_threads th
LEFT JOIN users u ON th.aid = u.id
LEFT JOIN forum_posts LastPost ON LastPost.ID = (SELECT Max(id) FROM forum_posts WHERE tid = th.id)
LEFT JOIN users LastPostUser ON LastPost.aid = LastPostUser.id
WHERE th.fid =2
ORDER BY th.sticky DESC , th.date DESC

---

oh, that's perfect! thanks.

so, were subqueries like the ones above really not inefficient at all? though, they were rather ugly either way.

How a query actually gets run depends on what methods the database engine uses to analyse it and optimise it, and for anything involving multiple tables you often find that it what it runs varies a bit from the way that the SQL statement seems to specify.

It is entirely possible that the database engine would actually treat my version as being identical to what you originally posted - because even tho the syntax is different, they effectively perform the same tasks (assuming that the database engine noticed the similarities in the sub-queries, and returned all of the required fields from those tables at once).

There are methods to find out what is actually being run (and thus modify the SQL statement to give the engine apt hints at ways to run it quicker), but I have no idea where they would be for MySQL. That kind of thing generally only really matters if speed is a huge issue tho, as most of the time just measuring the time difference between two versions of the statement is enough.

okay. well, thank you for the help either way! I refined my other similar queries to use joins instead of subqueries, as well. Not that I was ever against joins, but the whole multiple join thing was daunting.