Hi, my forum is reaching almost 5m register user which make the user table about 7.5GB. It also cause timeout when search user in admincp, usergroup management page, moderator permission, and it all seems related to user table. What should I do here to improve this.
Here is some slowquery log
# Query_time: 199.650164 Lock_time: 0.000047 Rows_sent: 69 Rows_examined: 157
SET timestamp=1523406231;
SELECT user.*, usergroup.usergroupid
FROM usergroup AS usergroup
INNER JOIN user AS user ON(user.usergroupid = usergroup.usergroupid OR FIND_IN_SET(usergroup.usergroupid, user.membergroupids))
WHERE (usergroup.adminpermissions & 1)
GROUP BY user.userid
ORDER BY user.username
/**getSuperGroups**/;
# Query_time: 130.292878 Lock_time: 0.000030 Rows_sent: 3989532 Rows_examined: 5073500
SET timestamp=1523406088;
SELECT `usergroupid`,`membergroupids` FROM user
WHERE `membergroupids` <> '';
Here is some slowquery log
# Query_time: 199.650164 Lock_time: 0.000047 Rows_sent: 69 Rows_examined: 157
SET timestamp=1523406231;
SELECT user.*, usergroup.usergroupid
FROM usergroup AS usergroup
INNER JOIN user AS user ON(user.usergroupid = usergroup.usergroupid OR FIND_IN_SET(usergroup.usergroupid, user.membergroupids))
WHERE (usergroup.adminpermissions & 1)
GROUP BY user.userid
ORDER BY user.username
/**getSuperGroups**/;
# Query_time: 130.292878 Lock_time: 0.000030 Rows_sent: 3989532 Rows_examined: 5073500
SET timestamp=1523406088;
SELECT `usergroupid`,`membergroupids` FROM user
WHERE `membergroupids` <> '';
Comment