MySQL Forums
Forum List  »  Optimizer & Parser

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
long time processing long query
6261
May 22, 2008 12:52PM
2647
May 25, 2008 08:09AM
2364
August 31, 2008 11:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.