High load and huge amount of data on StoreUser requests
Summary
Every Request on StoreUser page includes huge amounts (4MB) of data with significant server load.
Steps to reproduce
- Be responsible for a store
- Open the store page (like
/?page=fsbetrieb&id=13948) - Check Network Inspector of your Browser for the response size
What is the current bug behavior?
For the autocomplete functionality in the Team bearbeiten dialog, every request to the StoreUser site includes all visible foodsavers for the current user. In my case this are
- 84.354 Users
- 4MB of JS data ... on every request.
apparently this also leads to significant server load, almost all queries in mysql's slow log are due to this data. Executing the example query (responds with 212.739 users / ~10MB!) from the error message leads to a load ~300% and took 8 seconds.
What is the expected correct behavior?
Autocomplete should dynamically request data from the server as soon as you typed in some characters.
Relevant error messages and/or screenshots
MySQL slow log:
# Time: 201126 10:29:02
# User@Host: fsprod[fsprod] @ localhost []
# Thread_id: 51040541 Schema: fsprod QC_hit: No
# Query_time: 15.372987 Lock_time: 0.000033 Rows_sent: 84513 Rows_examined: 510065
# Rows_affected: 0 Bytes_sent: 2874482
# Tmp_tables: 2 Tmp_disk_tables: 1 Tmp_table_sizes: 4809088
# Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes
# Filesort: No Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
SET timestamp=1606382942;
SELECT DISTINCT
fs.`id`,
CONCAT(fs.`name`," ",fs.`nachname`," (",fs.`id`,")") AS value
FROM fs_foodsaver fs
INNER JOIN fs_foodsaver_has_bezirk hb
ON hb.foodsaver_id = fs.id
WHERE hb.bezirk_id IN(142,699,392,53,1973,2522,332,2336,3069,1977,2779,936,2827,2502,2770,2280,2519,1279,881,1,112,45,18,1324,1328,732,2518,741,2076,2052,117,940,2986,957,4,85,1925,1457,2556,1546,909,84,21,109,2739,83,305,50,902,2642,1974,1114,202,114)
AND fs.deleted_at IS NULL;
Relevant code line
Edited by chandi