Hi all,

I'm developing a forum-style script, and have hit a problem. I'd like to make it run really fast, so i'm trying to use as little MySQL queries as possible.

When a whole thread is listed (i.e. multiple posts), the script first grabs the content of each post from my "posts" table (this includes the user's ID).

Each user can include a little information about themselves, such as location, avatar, postcount, etc. These fields are in the "users" table.

For each post that's listed, the script looks at the UserID of who created the post. It then grabs the user's information from the user table.

An example of how to do this, would be similar to the following pseudo code:

-Grab posts where thread-id = whatever
-loop through all rows (posts)
-Grab user where user id = creator of post
-Show user information and post


Obviously the above method uses LOADS of queries, and i'd like to use as little as possible.

I have tried using a 'WHERE OR' clause to do this, however obviously when a user posts in the same thread twice then things get messed up (for example, WHERE id='3' OR id='3') only returns one result.

I hope I explained this OK,

If anyone has any ideas of how to achieve this (e.g. how is it done in VB and SMF) any help would be greatly appreciated.

Thanks.

Have you looked into JOIN queries as you will be able to retrieve data from multiple tables in the same query.

A JOIN is probably the best way to go. It will depend on your database structure, but from the method you listed there, you could likely cut that all down to 1 query.

If you find that you absolutely require multiple queries to retrieve related data your tables probably need some optimization.. You should find that you can almost always grab ALL the data you need in just one or maybe two queries.

Yes, you definately need to use a JOIN query. Try something like:

SELECT p.id,p.title,p.date,p.body, u.username,u.location,u.avatar,u.postcount,u.signature FROM tbl_posts AS p LEFT JOIN tbl_users AS u ON p.userid=u.id ORDER BY p.date DESC

Make sure you are using the LIMIT arguement to limit the amount of returns to the number of posts you are going to display.

Using the above example:

SELECT p.id,p.title,p.date,p.body, u.username,u.location,u.avatar,u.postcount,u.signature FROM tbl_posts AS p LEFT JOIN tbl_users AS u ON p.userid=u.id ORDER BY p.date DESC LIMIT 10 OFFSET 0


The number after offset would be determined by this equasion 10*(p-1)

where p is the page number.

Also assuming you are using an autonumber you can order by p.id to get the exact same order as p.date but it will probably be faster than using a date field in the order operator.

Thanks for the help!

Looks like JOIN is what I need. I'll start reading up on it, on first glance it looks complicated :)

Cheers.