OJS 3.1.1.4 Subscriptions Tabs Loading Extremely Slowly

caretecher · September 26, 2018, 8:31pm

Since we upgraded from OJS 2.4.8.2 to OJS 3.1.1.4; It takes an average of 5 minutes for this tab to load. Once it loads, Create New Submission popup is similarly sluggish. This is almost unusable.

The same process in OJS2 is extremely fast. (I still have a cloned instance of that up to compare.)

The error log does not show any errors only the PHP strict warnings.

Also this one about LazyLoad, which may be pertinent:

PHP Strict Standards: Declaration of PKPUsageEventPlugin::getEnabled() should be compatible with LazyLoadPlugin::getEnabled($contextId = NULL) in /home/aaaior5/public_html/ojs/lib/pkp/plugins/generic/usageEvent/PKPUsageEventPlugin.inc.php on line 386

17%20PM

israel.cefrin · September 27, 2018, 12:13am

Hi @caretecher

Have you check your browser console to check wich resource or request is taking so long to load?
In Chrome browser you are able to do so opening browser inspector (usually F12 key) and clicking in Network tab.

Best,
Israel

caretecher · September 27, 2018, 2:54am

@israel.cefrin - Thanks. I just did, it seems to be the script at:

/$$$call$$$/grid/subscriptions/individual-subscriptions-grid/fetch-grid?_=1538016544689

… it takes over 3.5 mins to load. It is trying to get 55 mb of data (8k entries) and trying to dump them all in one tab. The 50 entry filter on this tab (default) does not seem to work.

Not sure how to proceed.

42%20PM

crosfield · October 2, 2018, 5:06pm

I am also interested in the answer to this question. I have version 3.1.0.1 and fetch-grid processes are extremely slowly. For example, for User tab, it lasts 4.7 minutes. The problem exists in any interface elements where fetch-grid is used

asmecher · October 9, 2018, 8:38pm

Hi all,

This is filed at Subscriptions list is slow to load and does not apply row limit · Issue #4125 · pkp/pkp-lib · GitHub and I’ll post patches there shortly.

Regards,
Alec Smecher
Public Knowledge Project Team

crosfield · October 10, 2018, 12:09pm

Thanks, @asmecher, but this patch maybe helps in Subscription tab but for User tab it doesn’t help.

In all interface elements where fetch-grid is used it is extremely slow, for example, in the article metadata window with one author in the article it is also very slow (if there are more authors in the article then fetch-grid element is drawn more slowly).

Are there any debugging methods to find out the reasons for such slow work?

asmecher · October 10, 2018, 5:24pm

Hi @crosfield,

I’ve committed a fix for the subscriptions grids, but as far as I’m aware that problem shouldn’t affect the user grids. Are you sure your “items per page” and “number of pages” and “page links” settings are configured with reasonable numbers? (See the “Appearance” form for these.)

Regards,
Alec Smecher
Public Knowledge Project Team

crosfield · October 10, 2018, 6:42pm

Hi @asmecher!

On the Appearance tab I see the most common values (but I didn’t see “Number of pages” setting)

I think the reason is somewhere much deeper, but I do not know what methods to determine the causes of such behavior of fetch-grid processes

asmecher · October 12, 2018, 6:53pm

Hi @crosfield,

Can you confirm the absolute URL for the fetchGrid request that’s behaving badly? (Feel free to obscure the domain name etc.)

Thanks,
Alec Smecher
Public Knowledge Project Team

crosfield · October 13, 2018, 5:22am

Hi @asmecher, URL for fetch-grid process in the Users tab for example http://domain.name/$$$call$$$/grid/settings/user/user-grid/fetch-grid?oldUserId=0&_=1539407724434

asmecher · October 15, 2018, 4:02pm

Hi @crosfield,

Try looking just at the fetchGrid request and response, and temporarily turning on the debug option in config.inc.php. (This will cause database queries to be dumped to all clients, so turn this on only very briefly or use a test installation!) Watch for the main query that populates the grid; it’ll look something like…

SELECT DISTINCT u.*, COALESCE(ugl.setting_value, ugpl.setting_value) AS user_given, CASE WHEN ugl.setting_value <> '' THEN ufl.setting_value ELSE ufpl.setting_value END AS user_family FROM	users AS u LEFT JOIN user_settings us ON (us.user_id = u.user_id AND us.setting_name = 'affiliation') LEFT JOIN user_interests ui ON (u.user_id = ui.user_id) LEFT JOIN controlled_vocab_entry_settings cves ON (ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id) LEFT JOIN user_user_groups uug ON (uug.user_id = u.user_id) LEFT JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id) LEFT JOIN user_settings ugl ON (u.user_id = ugl.user_id AND ugl.setting_name = 'givenName' AND ugl.locale = 'en_US') LEFT JOIN user_settings ugpl ON (u.user_id = ugpl.user_id AND ugpl.setting_name = 'givenName' AND ugpl.locale = 'en_US') LEFT JOIN user_settings ufl ON (u.user_id = ufl.user_id AND ufl.setting_name = 'familyName' AND ufl.locale = 'en_US') LEFT JOIN user_settings ufpl ON (u.user_id = ufpl.user_id AND ufpl.setting_name = 'familyName' AND ufpl.locale = 'en_US') LEFT JOIN user_settings usgs ON (usgs.user_id = u.user_id AND usgs.setting_name = 'givenName') LEFT JOIN user_settings usfs ON (usfs.user_id = u.user_id AND usfs.setting_name = 'familyName') WHERE	1=1 AND ug.context_id = 1 ORDER BY user_family, user_given LIMIT 0,25

Can you see if yours matches this overall? (There will be a few variations as your version doesn’t match mine perfectly.)

Regards,
Alec Smecher
Public Knowledge Project Team

crosfield · October 15, 2018, 6:07pm

Hi @asmecher,

I turned on debugging and went to the Users tab. In addition to some UI elements of the backend, I got the output of debug:

output of debug

Sessions WHERE session_id = '9pjgbe00pu76s7oijs4a6dts67'  
(mysql): SELECT * FROM users WHERE user_id = 1  
(mysql): SELECT * FROM user_settings WHERE user_id = '1'  
(mysql): SELECT * FROM journals WHERE path = 'vestnik'  
(mysql): SELECT * FROM journal_settings WHERE journal_id = '1'  
(mysql): SELECT * FROM site  
(mysql): SET time_zone = '+3:00'  
(mysql): SELECT v.* FROM versions v LEFT JOIN plugin_settings ps ON lower(v.product_class_name) = ps.plugin_name AND ps.setting_name = 'enabled' AND (context_id = '1' OR v.sitewide = 1) WHERE v.current = 1 AND (ps.setting_value = '1' OR v.lazy_load <> 1)  
(mysql): SELECT last_run FROM scheduled_tasks WHERE class_name = 'plugins.generic.usageStats.UsageStatsLoader'  
(mysql): SELECT last_run FROM scheduled_tasks WHERE class_name = 'plugins.importexport.crossref.CrossrefInfoSender'  
(mysql): SELECT last_run FROM scheduled_tasks WHERE class_name = 'plugins.importexport.datacite.DataciteInfoSender'  
(mysql): SELECT last_run FROM scheduled_tasks WHERE class_name = 'plugins.importexport.doaj.DOAJInfoSender'  
(mysql): SELECT last_run FROM scheduled_tasks WHERE class_name = 'plugins.importexport.medra.MedraInfoSender'  
(mysql): SELECT last_run FROM scheduled_tasks WHERE class_name = 'lib.pkp.classes.task.ReviewReminder'  
(mysql): SELECT count(*) FROM notifications WHERE user_id = 1 AND date_read IS NULL AND level = 3  
(mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'defaultthemeplugin' AND context_id = 1  
(mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'defaultmanuscriptchildthemeplugin' AND context_id = 1  
(mysql): SELECT * FROM journals WHERE path = 'vestnik'  
(mysql): SELECT * FROM journal_settings WHERE journal_id = '1'  
(mysql): SELECT	ug.* FROM	user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE uug.user_id = 1  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '1'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '2'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '3'  
(mysql): SELECT * FROM navigation_menus WHERE context_id = 1  
(mysql): SELECT nmi.* FROM navigation_menu_item_assignments as nmh LEFT JOIN navigation_menu_items as nmi ON (nmh.navigation_menu_item_id = nmi.navigation_menu_item_id) WHERE nmh.navigation_menu_id = 2 ORDER BY nmh.seq  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '8'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '9'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '10'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '11'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '12'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '13'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '14'  
(mysql): SELECT nmi.*,nmh.navigation_menu_id,nmh.parent_id,nmh.seq, nmh.navigation_menu_item_assignment_id FROM navigation_menu_item_assignments as nmh LEFT JOIN navigation_menu_items as nmi ON (nmh.navigation_menu_item_id = nmi.navigation_menu_item_id) WHERE nmh.navigation_menu_id = 2 ORDER BY nmh.seq  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '145'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '146'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '147'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '148'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '149'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '150'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '151'  
(mysql): SELECT	DISTINCT ug.role_id FROM	user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE	uug.user_id = 1 AND ug.context_id = 1  
(mysql): SELECT	DISTINCT ug.role_id FROM	user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE	uug.user_id = 1  
(mysql): SELECT c.* FROM journals c WHERE	c.enabled = 1 OR c.journal_id IN (SELECT DISTINCT ug.context_id FROM user_groups ug JOIN user_user_groups uug ON (ug.user_group_id = uug.user_group_id) WHERE uug.user_id = 1) OR 1 IN (SELECT user_id FROM user_groups ug JOIN user_user_groups uug ON (ug.user_group_id = uug.user_group_id) WHERE ug.role_id = 1) ORDER BY seq  
(mysql): SELECT * FROM journal_settings WHERE journal_id = '1'  
(mysql): SELECT nmi.* FROM navigation_menu_item_assignments as nmh LEFT JOIN navigation_menu_items as nmi ON (nmh.navigation_menu_item_id = nmi.navigation_menu_item_id) WHERE nmh.navigation_menu_id = 3 ORDER BY nmh.seq  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '15'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '16'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '25'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '20'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '23'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '28'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '29'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '27'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '26'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '18'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '24'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '21'  
(mysql): SELECT * FROM navigation_menu_item_settings WHERE navigation_menu_item_id = '22'  
(mysql): SELECT nmi.*,nmh.navigation_menu_id,nmh.parent_id,nmh.seq, nmh.navigation_menu_item_assignment_id FROM navigation_menu_item_assignments as nmh LEFT JOIN navigation_menu_items as nmi ON (nmh.navigation_menu_item_id = nmi.navigation_menu_item_id) WHERE nmh.navigation_menu_id = 3 ORDER BY nmh.seq  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '152'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '153'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '154'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '155'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '156'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '157'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '158'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '159'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '160'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '161'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '162'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '163'  
(mysql): SELECT * FROM navigation_menu_item_assignment_settings WHERE navigation_menu_item_assignment_id = '164'  
(mysql): SELECT * FROM notifications WHERE user_id = 1 AND level = 1 ORDER BY date_created DESC  
(mysql): SELECT c.* FROM journals c WHERE	c.enabled = 1 OR c.journal_id IN (SELECT DISTINCT ug.context_id FROM user_groups ug JOIN user_user_groups uug ON (ug.user_group_id = uug.user_group_id) WHERE uug.user_id = 1) OR 1 IN (SELECT user_id FROM user_groups ug JOIN user_user_groups uug ON (ug.user_group_id = uug.user_group_id) WHERE ug.role_id = 1) ORDER BY seq  
(mysql): SELECT * FROM static_pages WHERE context_id = 1 AND path = 'management/settings/access'  
(mysql): SELECT	ug.* FROM	user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE uug.user_id = 1  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '1'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '2'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '3'

After that, nothing on the screen changed for more than 15 minutes. I got tired of waiting and interrupted the process. As you can see, there were no requests to fetch-grid. Also, the top menu items did not load.

Then I turned off debugging and refresh the page. Requests to fetch-grid immediately appeared in the Web Inspector but (as before) the loading time is very slow.

asmecher · October 15, 2018, 6:36pm

Hi @crosfield,

You won’t be able to reload the entire page with the debug setting turned On – it also injects SQL query text into AJAX responses, which will make the AJAX unparseable to the Javascript front-end. Try looking at just the fetchGrid response and loading it alone; you can use “Open in New Tab” in the inspector (or equivalent) to pull it out of its context and work with it separately.

Regards,
Alec Smecher
Public Knowledge Project Team

crosfield · October 15, 2018, 7:52pm

Hi @asmecher,

Thanks for the trick with “Open in New Tab”! Response for http://domain.name/$$$call$$$/grid/settings/user/user-grid/fetch-grid?oldUserId=0&_=1539632181095 is

Response

(mysql): SELECT * FROM sessions WHERE session_id = '9pjgbe00pu76s7oijs4a6dts67' (mysql): SELECT * FROM users WHERE user_id = 1 (mysql): SELECT * FROM user_settings WHERE user_id = '1' (mysql): SELECT * FROM journals WHERE path = 'vestnik' (mysql): SELECT * FROM journal_settings WHERE journal_id = '1' (mysql): SELECT * FROM site (mysql): SET time_zone = '+3:00' (mysql): SELECT v.* FROM versions v LEFT JOIN plugin_settings ps ON lower(v.product_class_name) = ps.plugin_name AND ps.setting_name = 'enabled' AND (context_id = '1' OR v.sitewide = 1) WHERE v.current = 1 AND (ps.setting_value = '1' OR v.lazy_load <> 1) (mysql): SELECT ug.* FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE uug.user_id = 1 (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '1' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '2' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '3' (mysql): SELECT count(*) FROM notifications WHERE user_id = 1 AND date_read IS NULL AND level = 3 (mysql): SELECT * FROM journals WHERE path = 'vestnik' (mysql): SELECT * FROM journal_settings WHERE journal_id = '1' (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'defaultthemeplugin' AND context_id = 1 (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'defaultmanuscriptchildthemeplugin' AND context_id = 1 (mysql): SELECT * FROM notifications WHERE user_id = 1 AND level = 1 ORDER BY date_created DESC (mysql): SELECT c.* FROM journals c WHERE c.enabled = 1 OR c.journal_id IN (SELECT DISTINCT ug.context_id FROM user_groups ug JOIN user_user_groups uug ON (ug.user_group_id = uug.user_group_id) WHERE uug.user_id = 1) OR 1 IN (SELECT user_id FROM user_groups ug JOIN user_user_groups uug ON (ug.user_group_id = uug.user_group_id) WHERE ug.role_id = 1) ORDER BY seq (mysql): SELECT ug.* FROM user_groups ug WHERE ug.context_id = 1 (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '2' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '3' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '4' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '5' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '6' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '7' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '8' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '9' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '10' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '11' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '12' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '13' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '14' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '15' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '16' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '17' (mysql): SELECT * FROM user_group_settings WHERE user_group_id = '18' (mysql): SELECT COUNT(*) FROM (SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings us ON (us.user_id = u.user_id AND us.setting_name = 'affiliation') LEFT JOIN user_interests ui ON (u.user_id = ui.user_id) LEFT JOIN controlled_vocab_entry_settings cves ON (ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id) LEFT JOIN user_user_groups uug ON (uug.user_id = u.user_id) LEFT JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id) LEFT JOIN user_settings usf ON (usf.user_id = u.user_id AND usf.setting_name = 'firstName' AND usf.locale = 'en_US' ) LEFT JOIN user_settings usl ON (usl.user_id = u.user_id AND usl.setting_name = 'lastName' AND usl.locale = 'en_US') WHERE 1=1 AND ug.context_id = 1 ) _ADODB_ALIAS_ LIMIT 1 (mysql): SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings us ON (us.user_id = u.user_id AND us.setting_name = 'affiliation') LEFT JOIN user_interests ui ON (u.user_id = ui.user_id) LEFT JOIN controlled_vocab_entry_settings cves ON (ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id) LEFT JOIN user_user_groups uug ON (uug.user_id = u.user_id) LEFT JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id) LEFT JOIN user_settings usf ON (usf.user_id = u.user_id AND usf.setting_name = 'firstName' AND usf.locale = 'en_US' ) LEFT JOIN user_settings usl ON (usl.user_id = u.user_id AND usl.setting_name = 'lastName' AND usl.locale = 'en_US') WHERE 1=1 AND ug.context_id = 1 ORDER BY usl.setting_value, usf.setting_value LIMIT 0,25 (mysql): SELECT * FROM user_settings WHERE user_id = '18' (mysql): SELECT * FROM user_settings WHERE user_id = '20' (mysql): SELECT * FROM user_settings WHERE user_id = '21' (mysql): SELECT * FROM user_settings WHERE user_id = '27' (mysql): SELECT * FROM user_settings WHERE user_id = '31' (mysql): SELECT * FROM user_settings WHERE user_id = '32' (mysql): SELECT * FROM user_settings WHERE user_id = '35' (mysql): SELECT * FROM user_settings WHERE user_id = '37' (mysql): SELECT * FROM user_settings WHERE user_id = '39' (mysql): SELECT * FROM user_settings WHERE user_id = '43' (mysql): SELECT * FROM user_settings WHERE user_id = '57' (mysql): SELECT * FROM user_settings WHERE user_id = '1' (mysql): SELECT * FROM user_settings WHERE user_id = '10' (mysql): SELECT * FROM user_settings WHERE user_id = '16' (mysql): SELECT * FROM user_settings WHERE user_id = '17' (mysql): SELECT * FROM user_settings WHERE user_id = '9' (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 18 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 20 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 21 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 27 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 31 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 32 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 35 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 37 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 39 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 43 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 57 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 10 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 16 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 17 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 9 AND ug.role_id = 1 (mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1 {"status":true,"content":"\n\n

I did not find anything similar to SELECT DISTINCT u.*, COALESCE etc. in the process response. Moreover, it looks unfinished. However, with debug mode turned off, the table with users is displayed successfully. But the whole process takes 2.8 - 3.6 minutes.

asmecher · October 15, 2018, 8:12pm

Hi @crosfield,

Thanks, that’s a good start. Essentially there’s going to be a single comprehensive query that fetches information about the whole list (which is the query I quoted), and then a series of lesser queries to fetch information about individual users. I’d suggest looking higher up in the debug output for the comprehensive query.

Regards,
Alec Smecher
Public Knowledge Project Team

crosfield · October 15, 2018, 8:40pm

Hi @asmecher,

Thanks, I understand you. I know a little bit about SQL queries (limited to a SELECT … FROM /INSERT … INTO). Above I pasted (in spoiler) the response from the server but I lost the line breaks, sorry. Below in spoiler is the server response which is readable

Server response

(mysql): SELECT * FROM sessions WHERE session_id = '[session hash]'  
(mysql): SELECT * FROM users WHERE user_id = 1  
(mysql): SELECT * FROM user_settings WHERE user_id = '1'  
(mysql): SELECT * FROM journals WHERE path = '[journal path]'  
(mysql): SELECT * FROM journal_settings WHERE journal_id = '1'  
(mysql): SELECT * FROM site  
(mysql): SET time_zone = '+3:00'  
(mysql): SELECT v.* FROM versions v LEFT JOIN plugin_settings ps ON lower(v.product_class_name) = ps.plugin_name AND ps.setting_name = 'enabled' AND (context_id = '1' OR v.sitewide = 1) WHERE v.current = 1 AND (ps.setting_value = '1' OR v.lazy_load <> 1)  
(mysql): SELECT	ug.* FROM	user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE uug.user_id = 1  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '1'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '2'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '3'  
(mysql): SELECT count(*) FROM notifications WHERE user_id = 1 AND date_read IS NULL AND level = 3  
(mysql): SELECT * FROM journals WHERE path = 'vestnik'  
(mysql): SELECT * FROM journal_settings WHERE journal_id = '1'  
(mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'defaultthemeplugin' AND context_id = 1  
(mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'defaultmanuscriptchildthemeplugin' AND context_id = 1  
(mysql): SELECT * FROM notifications WHERE user_id = 1 AND level = 1 ORDER BY date_created DESC  
(mysql): SELECT c.* FROM journals c WHERE	c.enabled = 1 OR c.journal_id IN (SELECT DISTINCT ug.context_id FROM user_groups ug JOIN user_user_groups uug ON (ug.user_group_id = uug.user_group_id) WHERE uug.user_id = 1) OR 1 IN (SELECT user_id FROM user_groups ug JOIN user_user_groups uug ON (ug.user_group_id = uug.user_group_id) WHERE ug.role_id = 1) ORDER BY seq  
(mysql): SELECT ug.* FROM	user_groups ug WHERE ug.context_id = 1  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '2'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '3'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '4'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '5'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '6'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '7'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '8'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '9'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '10'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '11'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '12'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '13'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '14'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '15'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '16'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '17'  
(mysql): SELECT * FROM user_group_settings WHERE user_group_id = '18'  
(mysql): SELECT COUNT(*) FROM (SELECT DISTINCT u.* FROM	users AS u LEFT JOIN user_settings us ON (us.user_id = u.user_id AND us.setting_name = 'affiliation') LEFT JOIN user_interests ui ON (u.user_id = ui.user_id) LEFT JOIN controlled_vocab_entry_settings cves ON (ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id) LEFT JOIN user_user_groups uug ON (uug.user_id = u.user_id) LEFT JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id) LEFT JOIN user_settings usf ON (usf.user_id = u.user_id AND usf.setting_name = 'firstName' AND usf.locale = 'en_US' ) LEFT JOIN user_settings usl ON (usl.user_id = u.user_id AND usl.setting_name = 'lastName' AND usl.locale = 'en_US') WHERE	1=1 AND ug.context_id = 1 ) _ADODB_ALIAS_ LIMIT 1  
(mysql): SELECT DISTINCT u.* FROM	users AS u LEFT JOIN user_settings us ON (us.user_id = u.user_id AND us.setting_name = 'affiliation') LEFT JOIN user_interests ui ON (u.user_id = ui.user_id) LEFT JOIN controlled_vocab_entry_settings cves ON (ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id) LEFT JOIN user_user_groups uug ON (uug.user_id = u.user_id) LEFT JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id) LEFT JOIN user_settings usf ON (usf.user_id = u.user_id AND usf.setting_name = 'firstName' AND usf.locale = 'en_US' ) LEFT JOIN user_settings usl ON (usl.user_id = u.user_id AND usl.setting_name = 'lastName' AND usl.locale = 'en_US') WHERE	1=1 AND ug.context_id = 1 ORDER BY usl.setting_value, usf.setting_value LIMIT 0,25  
(mysql): SELECT * FROM user_settings WHERE user_id = '18'  
(mysql): SELECT * FROM user_settings WHERE user_id = '20'  
(mysql): SELECT * FROM user_settings WHERE user_id = '21'  
(mysql): SELECT * FROM user_settings WHERE user_id = '27'  
(mysql): SELECT * FROM user_settings WHERE user_id = '31'  
(mysql): SELECT * FROM user_settings WHERE user_id = '32'  
(mysql): SELECT * FROM user_settings WHERE user_id = '35'  
(mysql): SELECT * FROM user_settings WHERE user_id = '37'  
(mysql): SELECT * FROM user_settings WHERE user_id = '39'  
(mysql): SELECT * FROM user_settings WHERE user_id = '43'  
(mysql): SELECT * FROM user_settings WHERE user_id = '57'  
(mysql): SELECT * FROM user_settings WHERE user_id = '1'  
(mysql): SELECT * FROM user_settings WHERE user_id = '10'  
(mysql): SELECT * FROM user_settings WHERE user_id = '16'  
(mysql): SELECT * FROM user_settings WHERE user_id = '17'  
(mysql): SELECT * FROM user_settings WHERE user_id = '9'  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 18 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 20 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 21 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 27 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 31 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 32 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 35 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 37 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 39 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 43 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 57 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 10 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 16 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 17 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 9 AND ug.role_id = 1  
(mysql): SELECT count(*) FROM user_groups ug JOIN user_user_groups uug ON ug.user_group_id = uug.user_group_id WHERE ug.context_id = 0 AND uug.user_id = 1 AND ug.role_id = 1  
{"status":true,"content":"\n\n

asmecher · October 15, 2018, 8:45pm

Hi @crosfield,

Thanks, that captures it. (I edited your post to obscure your session hash – probably best not to share that.)

The query in question is

SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings us ON (us.user_id = u.user_id AND us.setting_name = 'affiliation') LEFT JOIN user_interests ui ON (u.user_id = ui.user_id) LEFT JOIN controlled_vocab_entry_settings cves ON (ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id) LEFT JOIN user_user_groups uug ON (uug.user_id = u.user_id) LEFT JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id) LEFT JOIN user_settings usf ON (usf.user_id = u.user_id AND usf.setting_name = 'firstName' AND usf.locale = 'en_US' ) LEFT JOIN user_settings usl ON (usl.user_id = u.user_id AND usl.setting_name = 'lastName' AND usl.locale = 'en_US') WHERE	1=1 AND ug.context_id = 1 ORDER BY usl.setting_value, usf.setting_value LIMIT 0,25

Can you try that in a SQL client (e.g. phpMyAdmin) to see how long it takes?

Regards,
Alec Smecher
Public Knowledge Project Team

crosfield · October 15, 2018, 8:51pm

Hi @asmecher,

yes, 16 items in the table. This is the number of users I can see on the Users tab. Sorry, I will not give here a screenshot although there are only salt hashes of passwords, but there are visible nicknames and e-mail addresses

asmecher · October 15, 2018, 8:56pm

Hi @crosfield,

No screenshot necessary, thanks – just wondering how long it takes; this should be extremely quick.

Regards,
Alec Smecher
Public Knowledge Project Team

crosfield · October 15, 2018, 9:02pm

Hi @asmecher,

Yes, I wrote that a noticeable slowdown occurs even when displaying frame with single author in the Article Metadata tab. I want to understand whether there is a possibility of debugging in the code implementing the fetch-grid process using usual commands print/alert? But I do not understand anything about the implementation of this process