Hi
I have a root folder with 8000 subfolders in it, when I expand it in explorer takes 3-4 minutes to expand.
any work around?
Thanks
Farhad
No option to make this faster. Make sure the db is close to the server.
thanks for reply,
Database is Local.
Thats wiered , after struggling for 2 weeks I can fix the issue by restarting the vm for several times.
the process: restart and test, if its fast keep the machine on, it will be fine until you restart it again…
regards,
Farhad
Switch to MySQL and activate memcache caching and you should be better off.
Hi
Thanks for reply,
I’m already on mysql with best practices applied.
Regards,
Farhad
Happened again , after restart… just found the query causing this. This query gets stuck in mysql for like 300 seconds then fails. dunno why restarts fixing this yet.
SELECT /* 9B17C417DC2946EAA0A48740379615BFC2B7C1DE-0851-4352-AB60F8F907402A00getfoldersfortree */ folder_id, folder_name, folder_id_r, folder_of_user, folder_owner, folder_level, in_trash,link_path, username, perm, subhere, permfolder
FROM (
SELECT f.folder_id, f.folder_name, f.folder_id_r, f.folder_of_user, f.folder_owner, f.folder_level,f.in_trash,f.link_path,
ifnull(u.user_login_name,'Obsolete') as username,
CASE
WHEN EXISTS(
SELECT fg.folder_id_r
FROM raz1_folders_groups fg
WHERE fg.host_id = 1
AND fg.folder_id_r = f.folder_id
AND lower(fg.grp_permission) IN ('r','w','x')
AND fg.grp_id_r IN ('85797ED5-48BE-4E6F-91575B59FD651C94')
) THEN 'unlocked'
WHEN EXISTS(
SELECT fg2.folder_id_r
FROM raz1_folders_groups fg2
WHERE fg2.grp_id_r = '0'
AND fg2.folder_id_r = f.folder_id
AND fg2.host_id = 1
AND lower(fg2.grp_permission) IN ('r','w','x')
) THEN 'unlocked'
WHEN f.folder_owner = 'C2B7C1DE-0851-4352-AB60F8F907402A00' THEN 'unlocked'
WHEN f.folder_id = '1' THEN 'unlocked'
ELSE 'locked'
END AS perm
,
CASE
WHEN EXISTS(
SELECT *
FROM raz1_folders s1
WHERE s1.folder_id != f.folder_id
AND s1.folder_id_r = f.folder_id
ANd s1.in_trash = 'F'
AND s1.host_id = 1
AND s1.folder_owner = 'C2B7C1DE-0851-4352-AB60F8F907402A00'
LIMIT 1
) THEN 1
WHEN EXISTS(
SELECT *
FROM raz1_folders s2, raz1_folders_groups fg3
WHERE s2.folder_id != f.folder_id
AND s2.host_id = 1
AND s2.in_trash = 'F'
AND fg3.host_id = s2.host_id
AND s2.folder_id_r = f.folder_id
AND fg3.folder_id_r = s2.folder_id
AND lower(fg3.grp_permission) IN ('r','w','x')
AND fg3.grp_id_r IN ('85797ED5-48BE-4E6F-91575B59FD651C94')
LIMIT 1
) THEN 1
WHEN EXISTS(
SELECT *
FROM raz1_folders s3, raz1_folders_groups fg4
WHERE s3.folder_id != f.folder_id
AND s3.folder_id_r = f.folder_id
ANd s3.in_trash = 'F'
AND fg4.grp_id_r = '0'
AND fg4.folder_id_r = s3.folder_id
AND lower(fg4.grp_permission) IN ('r','w','x')
AND s3.host_id = 1
AND s3.host_id = fg4.host_id
LIMIT 1
) THEN 1
ELSE 0
END AS subhere
,
CASE
WHEN (
SELECT DISTINCT max(fg5.grp_permission)
FROM raz1_folders_groups fg5
WHERE fg5.host_id = 1
AND fg5.folder_id_r = f.folder_id
AND (
fg5.grp_id_r = '0'
OR fg5.grp_id_r IN ('85797ED5-48BE-4E6F-91575B59FD651C94')
)
) = 'R' THEN 'R'
WHEN (
SELECT DISTINCT max(fg5.grp_permission)
FROM raz1_folders_groups fg5
WHERE fg5.host_id = 1
AND fg5.folder_id_r = f.folder_id
AND (
fg5.grp_id_r = '0'
OR fg5.grp_id_r IN ('85797ED5-48BE-4E6F-91575B59FD651C94')
)
) = 'W' THEN 'W'
WHEN (
SELECT DISTINCT max(fg5.grp_permission)
FROM raz1_folders_groups fg5
WHERE fg5.host_id = 1
AND fg5.folder_id_r = f.folder_id
AND (
fg5.grp_id_r = '0'
OR fg5.grp_id_r IN ('85797ED5-48BE-4E6F-91575B59FD651C94')
)
) = 'X' THEN 'X'
WHEN (f.folder_owner = 'C2B7C1DE-0851-4352-AB60F8F907402A00') THEN 'X'
END as permfolder
FROM raz1_folders f LEFT JOIN users u ON u.user_id = f.folder_owner
WHERE
f.folder_id != f.folder_id_r
AND
f.folder_id_r = '09B665B432804EE89B8EFFF258970917'
AND (f.folder_is_collection IS NULL OR folder_is_collection = '')
AND
(
LOWER(ifnull(f.folder_of_user,'f')) != 't'
OR f.folder_owner = 'C2B7C1DE-0851-4352-AB60F8F907402A00'
)
AND f.host_id = 1
) as itb
WHERE itb.perm = 'unlocked'
regards,
Farhad
Update: No need for server restart, service mysql restart is triggering it.
Seems to be mysql 5.5.44 bug , upgrading to mysql 5.6 seems to fix the issue.