user manager times out...

[drummerkid08]

everything in my gallery seems to work fine.  however when i click on "users" in the admin panel, it always times out.  is there something i can do to fix this?

this is my site:  www.shineon-media.com/gallery
and if you need to login, here's a temp username: drummerkid08   password: sturunnr

[Nibbler]

You don't allow users to view the memberlist, so there is nothing for us to see at your site. How many users do you have ?

[drummerkid08]

okay, i've changed it so you can view the memberlist.  i'm not sure how many users i have, which is why i wanted to check the user manager.

[drummerkid08]

does it time out because i have too many users registered?? i need to add some people but it still times out.  is there a way to get to that page a different way?

[Joachim Müller]

How many users do you have ?

[Abbas Ali]

i'm not sure how many users i have, which is why i wanted to check the user manager.

[Joachim Müller]

phpMyAdmin?

[Abbas Ali]

I guess drummerkid08 doesn't know phpMyAdmin....

@drummerkid08: If you know phpMyAdmin or any other database management tool then browse your database and see how many records are there in cpg_users table (cpg_ should be replaced by prefix you have used).

[drummerkid08]

it says 1,675

[Stramm]

uhmm, I dunno.. but what for is

Code Select Expand

'' as user_active, in the SQL in function get_users

Code Select Expand

SELECT {$f['user_id']} as user_id, {$f['username']} as user_name, {$f['email']} as user_email, {$f['regdate']} as user_regdate, lastvisit as user_lastvisit, '' as user_active,

[drummerkid08]

i'm not quite sure....is it not supposed to be there?

[drummerkid08]

uhmm, I dunno.. but what for is

Code Select Expand

'' as user_active, in the SQL in function get_users

Code Select Expand

SELECT {$f['user_id']} as user_id, {$f['username']} as user_name, {$f['email']} as user_email, {$f['regdate']} as user_regdate, lastvisit as user_lastvisit, '' as user_active,

where exactly would i find that code.

[Stramm]

that was a question to the other devs... but you can find it in bridge/mybb.inc.php

[drummerkid08]

oh, okay.  so is there anything i can do?

[Nibbler]

If you are happy to PM me FTP and db access to your gallery then I can take a look for you.

Stramm, if you wish to discuss unrelated coding issues please do so on the dev or coding boards.

[drummerkid08]

okay. i pm'ed you.  thanks

[Nibbler]

It's the main query that's the problem here:

Code Select Expand


SELECT user_id AS user_id, user_name AS user_name, user_email AS user_email, UNIX_TIMESTAMP( user_regdate ) AS user_regdate, UNIX_TIMESTAMP( user_lastvisit ) AS user_lastvisit, user_active AS user_active, COUNT( pid ) AS pic_count, ROUND( SUM( total_filesize ) /1024 ) AS disk_usage, group_name, group_quota
FROM `shineon_copp1`.cpg143_users AS u
INNER JOIN cpg143_usergroups AS g ON u.user_group = g.group_id
LEFT JOIN cpg143_pictures AS p ON p.owner_id = u.user_id
GROUP BY user_id
ORDER BY user_regdate DESC
LIMIT 0 , 25


It takes several minutes to run due to the fact that it's not using any keys in the joins (see pic). All the keys are there that should be according to the schema.

I don't know what to suggest to solve this other than to get your mysql updated to the latest stable version. Maybe another supporter will come along and suggest something.

[drummerkid08]

hmmm...okay.  thanks for trying.  i don't know if i can update my mysql...i think my webhost has to do that...what's the newest version, because right now i have 4.1.14-standard.

[Nibbler]

I fixed it

Solution was to force the index in udb_base.inc.php

Code Select Expand


// Build SQL table, should work with all bridges
        $sql = "SELECT {$f['user_id']} as user_id, {$f['username']} as user_name, {$f['email']} as user_email, {$f['regdate']} as user_regdate, {$f['lastvisit']} as user_lastvisit, {$f['active']} as user_active, ".
               "COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota ".
               "FROM {$this->usertable} AS u ".
               "INNER JOIN {$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id ".
               "LEFT JOIN {$C['TABLE_PICTURES']} AS p FORCE INDEX ( owner_id ) ON p.owner_id = u.{$f['user_id']} ".
               $options['search'].
               "GROUP BY user_id " . "ORDER BY " . $sort_codes[$options['sort']] . " ".
               "LIMIT {$options['lower_limit']}, {$options['users_per_page']};";


[drummerkid08]

OMFG!!! Thank you sooooo much Nibbler, you're a lifesaver.  I really appreciate all your help.