Forum

Thread tagged as: Problem, Suggestions

Asset Tags

Hi Drew,

Sorry to make two threads. I've been trying to chase down a reason for a Perch 3 upgrade to have gotten a lot slower, and I've noticed that this particular client has over 3050 individual tags for assets. As a result anything that touches assets appears to be running very slowly (30-40s load times). The UI for the assets panel also seems to be struggling with the number of elements in the filter tab. I've removed the data and this does remove the delay.

This is after only a few months of the client managing their site so I am a little concerned about this over the long-term.

I wouldn't consider this a criticism, as no UI or DB should really be handling that many items well - I'm just wondering whether there is something that could be done as a hard limit or a paginated system that could cope better with this volume. It looks like a lot of the tags have come from images uploaded from Shutterstock.

At the moment I am considering truncating the resources_tags table to complete the upgrade, otherwise we are a bit stuck as adding non-text content is as a bit of a standstill.

James Wigger

James Wigger 0 points

  • 4 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

Are you talking about items in the 'By Tag' filter on the smartbar?

That's right, yes. This is an example of about 1/3 of the list, which took about 45 seconds to load.

It also affects uploading new assets, changing the view mode (list / grid). Generally anything that seems to pull in assets data. Running the queries from the debug-bar manually show reasonably fast speeds so I am guessing the issue is PHP related.

Time    Δ   Debug Message - Perch 3.0.5
6.7053  0   [1] SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='49ed646aaa5fcb514d34f92f8e2290d2' LIMIT 1
6.7058  0.0006  UPDATE perch2_users SET userHash='de50e9fad1e5f0762f581dbd93e0d117' WHERE userID='1'
6.7061  0.0003  [40] SELECT p.privKey FROM perch2_user_privileges p
6.7077  0.0016  [34] SELECT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID ASC
6.712   0.0043  Adding: Disqus
6.7122  0.0003  Adding: Heartbeat
6.7131  0.0009  [10] SELECT itemValue FROM perch2_menu_items WHERE itemType='app'
6.7188  0.0057  [30] SELECT SQL_CALC_FOUND_ROWS DISTINCT r1.*, r2.resourceFile AS thumb, r2.resourceWidth AS thumbWidth, r2.resourceHeight AS thumbHeight, r2.resourceDensity AS thumbDensity FROM perch2_resources r1 LEFT OUTER JOIN perch2_resources r2 ON r2.resourceParentID=r1.resourceID AND r2.resourceKey='thumb' AND r2.resourceAWOL!=1 WHERE r1.resourceKey='orig' AND r1.resourceAWOL=0 ORDER BY r1.resourceUpdated DESC, r1.resourceID DESC LIMIT 0, 30
6.8083  0.0895  [1405] SELECT FOUND_ROWS() AS `count`
6.8136  0.0053  [1] SELECT * FROM perch2_menu_items WHERE itemActive=1 AND itemType='menu' AND parentID=0 ORDER BY itemOrder ASC LIMIT 0, 1
6.8142  0.0006  [3] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=1 ORDER BY mi.itemOrder ASC
6.8154  0.0012  [1] SELECT itemTitle FROM perch2_menu_items WHERE itemType='app' AND itemValue='assets' LIMIT 1
6.8166  0.0012  [1] SELECT * FROM perch2_menu_items WHERE itemActive=1 AND itemType='menu' AND parentID=0 ORDER BY itemOrder ASC LIMIT 1, 10
6.817   0.0004  [2] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=2 ORDER BY mi.itemOrder ASC
6.8183  0.0013  File: /core/apps/assets/modes/list.post.php
6.8188  0.0004  [6] SELECT DISTINCT resourceType FROM perch2_resources WHERE resourceAWOL=0 AND resourceType !="" ORDER BY resourceType ASC
6.8317  0.013   [5] SELECT DISTINCT resourceBucket FROM perch2_resources WHERE resourceAWOL=0 AND resourceType !=""
6.8481  0.0164  [3058] SELECT * FROM perch2_resource_tags ORDER BY tagCount DESC
7.3096  0.4615  Queries: 16
7.3097  0.0001  Memory: 9.8043
Drew McLellan

Drew McLellan 2638 points
Perch Support

Yes, I could see how that many tags would be an issue.

What would you recommend at this point? I don't believe the client uses tags so would it be safe to truncate the tags and resources_to_tags tables as a temporary solution?

Drew McLellan

Drew McLellan 2638 points
Perch Support

Yes, that should be fine.