Project

General

Profile

Actions

Bug #7531

closed

Duplicate users in userlist

Added by krileon almost 5 years ago. Updated almost 5 years ago.

Status:
Closed
Priority:
Urgent
Assignee:
Target version:
Start date:
15 April 2019
Due date:
% Done:

100%

Estimated time:

Description

If a user has more than 1 usergroup they will display more than once in a userlist. This is due to the inner join of the usergroup mapping table. Adding DISTINCT back causes a performance decrease at the magnitude of 600x. Causing significant performance loss. Example as follows.

Current: 0.0311 seconds
GROUP BY: 20.4404 seconds
DISTINCT: 154.7703 seconds

https://www.joomlapolis.com/forum/153-professional-member-support/240857-list-entries-showing-multiple-times-after-recent-update#311833

Actions #1

Updated by krileon almost 5 years ago

SUBQUERY: 0.0311 seconds

Subquery optimizes away the same as current. This maybe the best, if not only, solution for optimal performance. It results in full indexing, including of the subquery, preventing full scans. If subquery can't be used (e.g. if the usergroup table is accessed by its join alias elsewhere) then GROUP BY is best.

Actions #2

Updated by krileon almost 5 years ago

  • Status changed from Assigned to Resolved
  • % Done changed from 0 to 100

Fixed in MR !1469 by changing the usergroups join to a subquery

Actions #3

Updated by krileon almost 5 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF