long time processing long query
Posted by:
Some One
Date: May 22, 2008 12:52PM
Hi,
I have a very long query that I need to process.
basically what I wanted to do is to Select the top N rows from each group. I used for that in the union all method. the query is very long and repeated itself, so I will show here only the first rows of it because the rest is the same (with different number of forums):
(
SELECT t.tid, t.title, t.forum_id, f.name
FROM (
SELECT tid, title, forum_id
FROM ibftopics
WHERE forum_id =2
AND tid !=7463
ORDER BY last_post DESC
LIMIT 10
) AS t, (
SELECT name
FROM ibfforums
WHERE id =2
) AS f
ORDER BY rand( )
LIMIT 1
)
UNION ALL (
SELECT t.tid, t.title, t.forum_id, f.name
FROM ibftopics AS t, ibfforums AS f
WHERE forum_id =2
AND tid !=7463
ORDER BY rand( )
LIMIT 10 , 2
)
UNION ALL (
SELECT t.tid, t.title, t.forum_id, f.name
FROM (
SELECT tid, title, forum_id
FROM ibftopics
WHERE forum_id =17
AND tid !=4026
ORDER BY last_post DESC
LIMIT 10
) AS t, (
SELECT name
FROM ibfforums
WHERE id =17
) AS f
ORDER BY rand( )
LIMIT 1
)
UNION ALL (
SELECT t.tid, t.title, t.forum_id, f.name
FROM ibftopics AS t, ibfforums AS f
WHERE forum_id =17
AND tid !=4026
ORDER BY rand( )
LIMIT 10 , 2
)
----
the results are
tid title forum_id name
7451 first 2 bil
517 second 2 gorg
2091 third 2 hillary
2198 fourth 17 barak
3597 fifth 17 john
3779 sisth 17 kondolisa
-----
what this query actually do is search the database for each forum and return for each one:
* one random result from the last 10 topics
* two random result from the others topics
this query take at least 1.5 seconds to process if only me ask for it. if I put it in my site and many people ask to see it, it can take about 30 seconds to return the results for each surfer!
please help - is there is a better to achieve this or optimize this query?
thanks a lot,
Dotan.