View Issue Details

Jump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0002730OXID eShop (all versions)2. ----- eShop backend (admin) -----public2011-04-08 14:052012-12-07 13:59
Reporterd3 Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status resolvedResolutionno change required 
Product Version4.4.8 revision 34028 
Summary0002730: AJAX list is almost unusable when there are lot of users.
DescriptionIf there are ~10k Users in the Shop, the ajax List runs into a timeout.
The query (below) uses 2 SELECTs
The inner SELECT sends 10k requests to the table oxuser.
The outer SELECT also sends 10k requests to the same table.
That means the query touches 10k * 10k requests.

The issue refers to the allocation popups.

SELECT count( * ) FROM oxuser /* outer SELECT */
WHERE oxuser.oxid not in ( /* inner SELECT */
    SELECT oxuser.oxid FROM oxuser, oxobject2group
    WHERE oxuser.oxid=oxobject2group.oxobjectid
    AND oxobject2group.oxgroupsid = '9554896bb47289081.08247109'
        AND oxuser.oxshopid = 'oxbaseshop'
)
AND oxuser.oxshopid = 'oxbaseshop'

the better way:
SELECT COUNT( * ) FROM ( /* innerer SELECT */
    SELECT oxuser.oxid, oxuser.oxshopid/* the rest of the outer SELECT */
        FROM oxuser, oxobject2group
    WHERE oxuser.oxid=oxobject2group.oxobjectid
    AND oxobject2group.oxgroupsid != '9554896bb47289081.08247109'
        AND oxuser.oxshopid = 'oxbaseshop'
) AS oxuser
WHERE oxuser.oxshopid = 'oxbaseshop'
Steps To Reproduce1. need 10k Users
2. go into [Administer Users]->[User Groups]->select a group->Button [Assign Users]
3. The list needs a lot of time or the list won't be loaded
TagsAJAX
Theme
BrowserAll
PHP Versionany
Database Versionany

Activities

birute_meilutyte

2011-04-29 16:05

reporter   ~0004425

@developers: check this issue from source code side

arvydas_vapsva

2011-09-29 12:20

reporter   ~0005273

Did you try your given example?

I generated test db with >60k records and first (original) query result is - 60008, your gives - 13.

Are you sure its ok?

Your given query selects count of users assigned to different groups and does not count users which are not assigned at all, additionally does not group users by oxid..

But original query does:
- first subquery selects ids of users, which are assigned to group (usually less than not assigned to current group)
- later by selecting all users skips thoose, which id is not in assigned array