Advanced search  

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]

Pages: [1]   Go Down

Author Topic: Slow query: listing "User galleries"  (Read 3714 times)

0 Members and 1 Guest are viewing this topic.

lierduh

  • Coppermine newbie
  • Offline Offline
  • Posts: 3
    • Aussie Phorums
Slow query: listing "User galleries"
« on: November 28, 2005, 03:02:54 am »

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);
« Last Edit: November 28, 2005, 03:36:22 am by Nibbler »
Logged

Nibbler

  • Guest
Re: Slow query: listing "User galleries"
« Reply #1 on: November 28, 2005, 03:34:55 am »

Good call, committed fixes to cvs.

Number of page tabs fix is to alter this query

Code: [Select]
        // 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]
// 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;";
Logged
Pages: [1]   Go Up
 

Page created in 0.015 seconds with 18 queries.