Slow query: listing "User galleries"

[lierduh]

I am using Coppermine 1.42 with vBulletin. Due to the number of users we have (>20,000), the query which lists the User galleries takes a long time (more than a few seconds).

In bridge/udb_base.inc.php, around line 454.

"INNER JOIN {$this->usertable} as u on u.{$f['user_id']} + " . FIRST_USER_CAT . " = a.category ";
(needs to query the entire user table)

Please change to

"INNER JOIN {$this->usertable} as u on u.{$f['user_id']} = a.category - " . FIRST_USER_CAT . " ";

Also the page number of the User Galleries is wrong for normal users (Admin is alright). I think the number of albums were calculated using the total number of albums, including the empty ones. So it is possible to have say 7 pages breadcomb links, while the last one or two do not work. To make things worse, the $PAGE seems to be cookied. So the user may never get to see the User Gallery anymore once they click an "empty" page link. I have to use some temporarily code to reset $PAGE to 1 when the album list returns an empty set:

$cpg_udb->list_users_query($user_count);

[Nibbler]

Good call, committed fixes to cvs.

Number of page tabs fix is to alter this query

Code Select Expand


        // Get the total number of users with albums
        $sql  = "select null ";
        $sql .= "from {$CONFIG['TABLE_ALBUMS']} as p ";
        $sql .= "where ( category>".FIRST_USER_CAT." $forbidden) ";
        $sql .= "group by category;";


to

Code Select Expand


// Get the total number of users with albums
        $sql  = "select null ";
        $sql .= "from {$CONFIG['TABLE_ALBUMS']} as p ";
        $sql .= " INNER JOIN {$CONFIG['TABLE_PICTURES']} AS pics ON pics.aid = p.aid ";
$sql .= "where ( category>".FIRST_USER_CAT." $forbidden) ";
        $sql .= "group by category;";