MySQL Forums
Forum List  »  Optimizer & Parser

Re: Executing same query in 1 moment
Posted by: Rick James
Date: November 22, 2011 12:05AM

Build a "summary table". It would have:
* day -- grouping data on day of SubAddDate
* SubLanguageID
* UserId
* UserNickName
* ct -- derived from COUNT(UserID)
* Latest (within `day`) -- derived from max(a.SubAddDate)
* IDSubtitle
It would contain only information about UserID > 0 AND SubEnabled = 1.
The first 3 fields would be the PRIMMARY KEY.
Every night add rows to this summary table based on the data for yesterday.
Run the 5 second queries against this summary table (with suitable adjustments); the queries should be so fast that 20 users will probably not stumble over each other.

The new query would be (approximately):
SELECT SUM(ct) as cnt, UserID, UserNickName, max(Latest) as Latest, max(IDSubtitle) as IDSubtitle
FROM search_cache
WHERE SubLanguageID in ('eng', 'pol')
AND DATE_SUB(CURDATE(),INTERVAL 1 month) <= day
GROUP BY UserID
ORDER BY cnt DESC
LIMIT 5

I'm vague on your intent behind COUNT(UserID); this may cause some trouble.

Options: ReplyQuote


Subject
Views
Written By
Posted
2088
November 19, 2011 09:27AM
888
November 20, 2011 04:42PM
920
November 20, 2011 10:31PM
Re: Executing same query in 1 moment
821
November 22, 2011 12:05AM
911
November 21, 2011 12:42AM


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.