mysql: slow order by query

[Doug]

Can some one help me? I am using mysql 4.0.20a

Here's the situation (I know it sounds kinda strange, but please follow
me here):

I'm trying to create a messaging forum. I have Forums (for example
sports), within those forums, I have topics, for example "Who will win
the World Series." There is another table which contains threads,
within the topics. I also have a hit_counter table which has an
object_id, object_type, and num_hits. I want to sort the results by
num_hits.

So, if I try to do something like this:

SELECT T.title, H.num_hits FROM topics as T, hit_counter as H, threads
as R WHERE T.forum_id=1 AND T.topic_id=R.topic_id AND
H.object_id=R.thread_id AND H.object_type='THREAD'ORDER BY H.num_hits DESC

when I do an EXPLAIN SELECT it tells me:

Using where; Using temporary; Using filesort

which is SLOW.

Here's a short version of what I want to happen:

Topics picked by forum_id
Threads picked by thread.topic_id
hit_counter picked by thread_id AND
The whole thing sorted by hit_counter.num_hits.

Is there a way to do this any faster that doesn't sort a temporary file?
And I already have plenty indexes (I have one on forum_id, topic_id,
num_hits).

-d

[Octavianus S]

Hi Doug,

Maybe u should try to limit your query by adding : "LIMIT 0,50" (to
limit only 50 records) so that the data request should be smaller and
there is no more *SLOW* !

I hope it can help you.. :p

God Bless,

Octavianus S

Doug <do*****@XXXXremovetheXearthlink.net> wrote in message news:<0q****************@newsread3.news.atl.earthl ink.net>...

Can some one help me? I am using mysql 4.0.20a

Here's the situation (I know it sounds kinda strange, but please follow
me here):

I'm trying to create a messaging forum. I have Forums (for example
sports), within those forums, I have topics, for example "Who will win
the World Series." There is another table which contains threads,
within the topics. I also have a hit_counter table which has an
object_id, object_type, and num_hits. I want to sort the results by
num_hits.

So, if I try to do something like this:

SELECT T.title, H.num_hits FROM topics as T, hit_counter as H, threads
as R WHERE T.forum_id=1 AND T.topic_id=R.topic_id AND
H.object_id=R.thread_id AND H.object_type='THREAD'ORDER BY H.num_hits DESC

when I do an EXPLAIN SELECT it tells me:

Using where; Using temporary; Using filesort

which is SLOW.

Here's a short version of what I want to happen:

Topics picked by forum_id
Threads picked by thread.topic_id
hit_counter picked by thread_id AND
The whole thing sorted by hit_counter.num_hits.

Is there a way to do this any faster that doesn't sort a temporary file?
And I already have plenty indexes (I have one on forum_id, topic_id,
num_hits).

-d