Serios problem - Mysql Query too hard on database

Hi,

My website has few thousands visitors a day. I have experienced error 503 on my hosting. The administrator of hosting informed me that there is a query in my scripts that are too time consuming.

This is the query:

SELECT g.*, r.*, COUNT(*) AS cnt
                            FROM pas_games AS g, pas_related AS r WHERE r.ip IN
(SELECT DISTINCT ip FROM pas_related WHERE g.active=1 AND game_id=2399) AND
g.id=r.game_id AND g.id<>2399
 GROUP BY r.game_id
 ORDER BY cnt DESC
 LIMIT 6

Finishing of this query takes several seconds (4 to 6 seconds), if there are more of them it takes even longer. In case there are some other queries that want to work with the same table, the time is being prolonged even more. The maximum number of php processes is 20 and when it reaches this status, the 503 error comes.

The query is in file:content/blocks/block_related.php

I need this fixed as soon as possible. i am not sure if author of this script did not count on the possibility some user mayb eb using script for website few thousands visitors visit each day... It's normal that I have 60-70 users at time on website!

I saw this problem before... it's wrong settings of the "RELATED" block. In your case, i.e. in case a site is getting good traffic, this block's logs should expire faster than normally (by default the TTL is 1 day), so the line

Code:

// Remove obsolete records
$delta = time() - $cRelatedDays*24*3600;

can be replaced by

Code:

// Remove obsolete records
$delta = time() - 3600;  // seconds, you can change this value to more appropriate for your site. Just make sure this block shows relevant results

And constant $cRelatedDays can be removed from the beginning of the block as obsolete.

So If I wanted TTL of 4 hours, what should I put into $delta ?

4 hours = 60*60*4 = 14400 seconds and the code is
$delta = time() - 14400;