Up for a challenge? solve this tricky query if you can... need help :)

I`ve created my own phorum using php/mysql, but as the site
is growing
rapidly I`m having trouble with slow processing when it
comes to
viewing threads on my discussion index page. On the server
the MySQL
version is 4.0, so subquerys are not allowed.

My problem is this:

phorum_index.php is supposed to list all thread names in the
following
order:
threads with unread submissions first, then sorted by the
latest
submission in that particular thread.

my tables are as follow (only relevant attributes):
users: user_id, first_name
thread: thread_id, thread_name
submissions: submission_id, thread_id, date, time
unread: user_id, thread_id, submission_id

the index-page lists thread_name, number of posts (and new
posts) and
who and when the last post was posted.

As i didn`t expect the site to grow to a significant size I
didn`t care
much in optimizing and now this listing is done in
php-software
combined with a lot of querys...(wich makes it slow!!!)

What would at least help me a lot to start with is to get a
query that
lists thread_id in the correct order (by date desc, time
desc)!

At the moment I`m having trouble with use of DISTINCT
combined wit
ORDER BY and GROUP BY

Thanx for any suggestions!


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "PHP & MySQL" group.
To post to this group, send email to phpmysqlgooglegroups.com
To unsubscribe from this group, send email to
phpmysql-unsubscribegooglegroups.com
For more options, visit this group at http://groups
.google.com/group/phpmysql
-~----------~----~----~----~------~----~------~--~---

Although it may well be possible, I can't think how to do
it in a
single query. Also, I'm not quite sure as to the extent of
4.0's
limitations.

The only thing I can suggest is two queries. The first
creates a
temporary view and the second LEFT JOINS submissions onto a
distinct
set of results - something like this...

Query 1:

CREATE VIEW tempview AS SELECT
u.user_id,first_name,t.thread_id,s.submission_id,s.submit_ti
me,IF
(unread.submission_id
 IS NULL,'read','unread') AS 'read/unread' FROM users
u,threads
t,submissions s LEFT JOIN unread ON unread.submission_id =
s.
submission_id WHERE s.thread_id=t.thread_id AND
u.user_id='1' ORDER BY
'read/unread' DESC, submit_time DESC;

Query 2:

SELECT
DISTINCT(t1.thread_id),t2.submission_id,t2.submit_time FROM
tempview t1 LEFT JOIN tempview t2 ON t1.thread_id =
 t2.thread_id;


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "PHP & MySQL" group.
To post to this group, send email to phpmysqlgooglegroups.com
To unsubscribe from this group, send email to
phpmysql-unsubscribegooglegroups.com
For more options, visit this group at http://groups
.google.com/group/phpmysql
-~----------~----~----~----~------~----~------~--~---