Re: Executing same query in 1 moment

[Brano Gerzo]

Thanks for answer Rick.

The queries are statistic queries, so it is normal, they executes longer. Here are some examples:

SELECT SQL_CACHE COUNT(UserID) as cnt, UserID, UserNickName, max(a.SubAddDate) as Latest, max(IDSubtitle) as IDSubtitle
FROM search_cache a
WHERE UserID > 0
AND SubLanguageID in ('eng', 'pol')
AND SubEnabled = 1
AND DATE_SUB(CURDATE(),INTERVAL 1 month) <= SubAddDate
GROUP BY UserID
ORDER BY cnt DESC
LIMIT 5

I got few queries like this, I got indexes right, everything should be ok. This query takes around 5 seconds, which is not so bad. Problem is when I got 20 users in the same time and wants results for this query. I can see mysql is executing 20 times this query (query cache doesnt help here in this case), so my db box is loaded.

Anyway, I was thinking to avoid situations like this in a way:, I am caching results in memcache...

- check the memcache, if it is going to expire soon or id doesnt exists, make a lock in memcache, so other query will not go to mysql, execute this query in sql, cache it into memcache, release the lock.

I think this should work.