Database Server Down after Installing Achivements

Hi,

After installing the achievement module last night, my site went down frequently every 15 minutes. After investigating, there are some achievement has slow queries that is causing too many connections because the query  taking up too much time is as below. I disabled the achievements that has query related issues, and as per the logs, here are the queries:

SET timestamp=1342445573; 

SELECT `ID` FROM `Profiles` WHERE `Status` = 'Active' AND `ID` NOT IN (SELECT `member_id` FROM `modzzz_achieve_main` WHERE `achieve_id`=2 AND YEAR(FROM_UNIXTIME(`created`)) = YEAR(CURDATE())) AND MONTH(`DateLastLogin`) = 1 AND DAY(`DateLastLogin`) = 1 AND YEAR(`DateLastLogin`) = YEAR(CURDATE()); 

# Time: 120716 8:33:41 

# Query_time: 48.740152 Lock_time: 0.001693 Rows_sent: 0 Rows_examined: 198697067 

SET timestamp=1342445621; 

SELECT `Owner`, count(`Owner`) as Cnt FROM `bx_photos_main` WHERE `Status` = 'approved' AND `Owner` NOT IN (SELECT `member_id` FROM `modzzz_achieve_main` WHERE `achieve_id`=4) GROUP BY `Owner` HAVING `Cnt` >= 20; 

# Time: 120716 8:36:29 

# Query_time: 167.698758 Lock_time: 0.000089 Rows_sent: 0 Rows_examined: 685548036 

SET timestamp=1342445789; 

SELECT COUNT(`Owner`) AS Cnt, `Owner` FROM `sys_albums` WHERE `Owner` NOT IN (SELECT `member_id` FROM `modzzz_achieve_main` WHERE `achieve_id`=5) AND `Type`='bx_photos' AND `Status`='active' AND `Caption` != 'Hidden' GROUP BY `Owner` HAVING COUNT(`Owner`) >= 5; 

# Time: 120716 8:37:54 

# Query_time: 172.596160 Lock_time: 0.000041 Rows_sent: 0 Rows_examined: 698829006 

SET timestamp=1342445874; 

SELECT `ID` FROM `Profiles` WHERE `Status` = 'Active' AND `ID` NOT IN (SELECT `member_id` FROM `modzzz_achieve_main` WHERE `achieve_id`=2 AND YEAR(FROM_UNIXTIME(`created`)) = YEAR(CURDATE())) AND MONTH(`DateLastLogin`) = 1 AND DAY(`DateLastLogin`) = 1 AND YEAR(`DateLastLogin`) = YEAR(CURDATE()); 

# Time: 120716 8:39:37 

# Query_time: 187.661077 Lock_time: 0.000024 Rows_sent: 0 Rows_examined: 764173731 

SET timestamp=1342445977; 

SELECT COUNT(`Owner`) AS Cnt, `Owner` FROM `sys_albums` WHERE `Owner` NOT IN (SELECT `member_id` FROM `modzzz_achieve_main` WHERE `achieve_id`=7) AND `Type`='bx_sounds' AND `Status`='active' AND `Caption` != 'Hidden' GROUP BY `Owner` HAVING COUNT(`Owner`) >= 5; 

use okatanan_dol703; 

SET timestamp=1342445573; 

SELECT `ID` FROM `Profiles` WHERE `Status` = 'Active' AND `ID` NOT IN (SELECT `member_id` FROM `modzzz_achieve_main` WHERE `achieve_id`=2 AND YEAR(FROM_UNIXTIME(`created`)) = YEAR(CURDATE())) AND MONTH(`DateLastLogin`) = 1 AND DAY(`DateLastLogin`) = 1 AND YEAR(`DateLastLogin`) = YEAR(CURDATE()); 

# Time: 120716 8:33:41 

# User@Host: okatanan_dol703[okatanan_dol703] @ localhost [] 

# Query_time: 48.740152 Lock_time: 0.001693 Rows_sent: 0 Rows_examined: 198697067 

SET timestamp=1342445621; 

SELECT `Owner`, count(`Owner`) as Cnt FROM `bx_photos_main` WHERE `Status` = 'approved' AND `Owner` NOT IN (SELECT `member_id` FROM `modzzz_achieve_main` WHERE `achieve_id`=4) GROUP BY `Owner` HAVING `Cnt` >= 20; 

# Time: 120716 8:36:29 

# User@Host: okatanan_dol703[okatanan_dol703] @ localhost [] 

# Query_time: 167.698758 Lock_time: 0.000089 Rows_sent: 0 Rows_examined: 685548036 

SET timestamp=1342445789; 

SELECT COUNT(`Owner`) AS Cnt, `Owner` FROM `sys_albums` WHERE `Owner` NOT IN (SELECT `member_id` FROM `modzzz_achieve_main` WHERE `achieve_id`=5) AND `Type`='bx_photos' AND `Status`='active' AND `Caption` != 'Hidden' GROUP BY `Owner` HAVING COUNT(`Owner`) >= 5; 

# Time: 120716 8:37:54 

# User@Host: okatanan_dol703[okatanan_dol703] @ localhost [] 

# Query_time: 172.596160 Lock_time: 0.000041 Rows_sent: 0 Rows_examined: 698829006 

SET timestamp=1342445874; 

SELECT `ID` FROM `Profiles` WHERE `Status` = 'Active' AND `ID` NOT IN (SELECT `member_id` FROM `modzzz_achieve_main` WHERE `achieve_id`=2 AND YEAR(FROM_UNIXTIME(`created`)) = YEAR(CURDATE())) AND MONTH(`DateLastLogin`) = 1 AND DAY(`DateLastLogin`) = 1 AND YEAR(`DateLastLogin`) = YEAR(CURDATE()); 

# Time: 120716 8:39:37 

# User@Host: okatanan_dol703[okatanan_dol703] @ localhost [] 

# Query_time: 187.661077 Lock_time: 0.000024 Rows_sent: 0 Rows_examined: 764173731 

SET timestamp=1342445977; 

SELECT COUNT(`Owner`) AS Cnt, `Owner` FROM `sys_albums` WHERE `Owner` NOT IN (SELECT `member_id` FROM `modzzz_achieve_main` WHERE `achieve_id`=7) AND `Type`='bx_sounds' AND `Status`='active' AND `Caption` != 'Hidden' GROUP BY `Owner` HAVING COUNT(`Owner`) >= 5; 

Looking forward to optimize the above queries in order to enable the related achievements. 

Best Regards,

Okatanani 

Hello

I think it would be better to contact with the vendor of the Achivements module or post the question on mod's support forum.