Slow query: listing "User galleries" Slow query: listing "User galleries"
 

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Main Menu

Slow query: listing "User galleries"

Started by lierduh, November 28, 2005, 03:02:54 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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


        // 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


// 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;";