MantisBT

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0003493ATutorAdminpublic2008-07-15 07:192009-07-21 08:51
Reporterharris 
Assigned Tocindy 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOSOS Version
Product Version 
Target VersionFixed in Version1.6.2 
Summary0003493: Admin's user page is slowed by the master list
DescriptionWhen AT_master_list's size is huge (say 30k+), then the two left join statements in that page will clog up the loading speed, it may clogged up the server as well, resulting a timeout.

Additional Informationtemp fix:

Many entries in the master list have member_id = 0, which doesn't do anything with a left join. If we can filter these out before joining, the speed will be improved greatly. ie. add 'member_id <> 0' into the sql statements
TagsNo tags attached.
Affects versionSVN
SVN Revision#
Attached Files

- Relationships

-  Notes
(0003061)
harris (developer)
2008-07-15 07:20

since 1.5.4
(0003073)
harris (developer)
2008-07-22 05:17

In our admin/users.php
line 159:
$sql = "SELECT COUNT(M.member_id) AS cnt FROM ".TABLE_PREFIX."members M LEFT JOIN ".TABLE_PREFIX."master_list L USING (member_id) WHERE M.status $status AND $search AND $searchid AND $last_login_days";

Change that to:
        $sql = "SELECT COUNT(M.member_id) AS cnt FROM ".TABLE_PREFIX."members M LEFT JOIN ".TABLE_PREFIX."master_list L USING (member_id) WHERE M.status $status AND $search AND $searchid AND $last_login_days AND L.member_id <> 0";

Also,
line 182:
    $sql = "SELECT M.member_id, M.login, M.first_name, M.second_name, M.last_name, M.email, M.status, M.last_login+0 AS last_login, L.public_field FROM ".TABLE_PREFIX."members M LEFT JOIN ".TABLE_PREFIX."master_list L USING (member_id) WHERE M.status $status AND $search AND $searchid AND $last_login_days ORDER BY $col $order LIMIT $offset, $results_per_page";

Change that to:
        $sql = "SELECT M.member_id, M.login, M.first_name, M.second_name, M.last_name, M.email, M.status, M.creation_date+0 AS creation_date, M.last_login+0 AS last_login, L.public_field FROM ".TABLE_PREFIX."members M LEFT JOIN ".TABLE_PREFIX."master_list L USING (member_id) WHERE M.status $status AND $search AND $searchid AND $last_login_days AND L.member_id <> 0 ORDER BY $col $order LIMIT $offset, $results_per_page";
(0003198)
greg (administrator)
2008-09-25 08:47

bump, fix before 1.6.2
(0003420)
cindy (administrator)
2008-12-02 11:22

SVN revision: 8259

affected script: admin/users.php

solution: reduce left join table (master_list) size

- Issue History
Date Modified Username Field Change
2008-07-15 07:19 harris New Issue
2008-07-15 07:19 harris Affects version => SVN
2008-07-15 07:20 harris Note Added: 0003061
2008-07-22 05:17 harris Note Added: 0003073
2008-09-25 08:47 greg Note Added: 0003198
2008-12-02 11:22 cindy Status new => resolved
2008-12-02 11:22 cindy Fixed in Version => 1.6.2
2008-12-02 11:22 cindy Resolution open => fixed
2008-12-02 11:22 cindy Assigned To => cindy
2008-12-02 11:22 cindy Note Added: 0003420
2009-07-21 08:51 greg Status resolved => closed


Copyright © 2000 - 2017 MantisBT Team
Powered by Mantis Bugtracker