MySQL order by problem

lunar

Hello, I am the Webmaster of a german Board Community and I have a problem with an SQL Query.

I have been searching for like a week, but I wasn't able to find a solution for my problem, thats why I have to decided to post my question here.

I use the following query to show the last posts been made on a board ($siteid ist the ID of the Board and $show the number of posts to display):

SELECT
Forum_Posts.dateline,
Forum_Posts.threadid,
Forum_Posts.username,
Forum_Posts.userid,
Forum_Threads.title,
Forum.title as forumtitle,
Forum.forumid,
Forum.viewflag
FROM Forum
LEFT JOIN Forum_Threads using(forumid)
LEFT JOIN Forum_Posts using(threadid)
WHERE Forum.siteid='$siteid' order by dateline desc LIMIT $show

This query is rather slow an it takes about 3-4 Seconds to get the result out of a database of 400.000 posts.

If I remove however the order tag (order by dateline desc) the query becomes really fast.

The explain funtions tells me that mysql hast to use a temporary table and filesort to create the result.

Any suggestions?

barand

Does the table have an index on dateline?

herve

Hi,

I don't know what you have in your Forum_Threads
table, but otherwise, you could "simply" copy
the "siteid" from the "Forum" table in the "Forum_Posts" table.

Either by really moving it (ie, killing the Forum_Threads table), or just duplicating the data.

Yes, it is redundancy, but sometimes you need it 🙂

Result is a much simplier request, something like that :

SELECT
Forum_Posts.siteid,
Forum_Posts.dateline,
Forum_Posts.threadid,
Forum_Posts.username,
Forum_Posts.userid,
FROM Forum_Posts
WHERE Forum_Posts.siteid='$siteid' order by dateline desc LIMIT $show

which should be much faster

regards,
Hervé.

lunar

Originally posted by barand
Does the table have an index on dateline?

Here are the indexes I've put on the tables:

Forum
forumid - primary
siteid - index

Forum_Threads
threadid - primary
forumid - index

Forum_Posts
postid - primary
threadid - index
userid - index

barand

If it's a query you need to do frequently, try adding a dateline DESC index on the forum_posts table.

lunar

Originally posted by herve
Hi,

I don't know what you have in your Forum_Threads
table, but otherwise, you could "simply" copy
the "siteid" from the "Forum" table in the "Forum_Posts" table.

Either by really moving it (ie, killing the Forum_Threads table), or just duplicating the data.

Yes, it is redundancy, but sometimes you need it 🙂

Result is a much simplier request, something like that :

SELECT
Forum_Posts.siteid,
Forum_Posts.dateline,
Forum_Posts.threadid,
Forum_Posts.username,
Forum_Posts.userid,
FROM Forum_Posts
WHERE Forum_Posts.siteid='$siteid' order by dateline desc LIMIT $show

which should be much faster

regards,
Hervé.

I could add the siteid directly to the posts table, thats a good idea, but I still need the Forum and the Thread table to get the Name of the Forum/Thread, here is the page that is generated by the script:

http://www.nexusboard.net/shownew.php3?siteid=918

Yes, redundancy is sometimes really usefull, I have been able to divide the loading time of a couple of pages by 2, just by creating a couple of extra tables.

I could maybe create a table called "Forum_Lastposts" where I would always store the last 10 Posts of a Board, but I would prefere changing my query.

lunar

Originally posted by barand
If it's a query you need to do frequently, try adding a dateline DESC index on the forum_posts table.

I dont think, this will by usefull, as the dateline is not used in the where clause, it is only needed to sort the results.

I have tried it but there was no significant changing.

🙁

herve

Ok,

so something like that :

SELECT
Forum_Posts.siteid,
Forum_Posts.dateline,
Forum_Posts.threadid,
Forum_Posts.username,
Forum_Posts.userid,
Forum.Name
FROM Forum_Posts, Forum
WHERE (Forum_Posts.siteid = '$siteid') AND
(Forum.siteid = '$siteid')
ORDER BY dateline DESC LIMIT $show

and you can add Forum_Threads if you need something from that table too.

The main down factor is the JOIN I think, so
if we can avoid it...

Regards,
Hervé.