Long query times viewing assets app
Apologies for posting so many threads recently. This last build seems to be exposing some issues in Perch that only occur with very large amounts of data.
I have imported the assets from the previous version of a site, there is a lot of historical data here which means there are now around 157 pages in the assets app. These are all required files and can't be pruned. There are 12,000+ rows in the database. This seems to be producing a load time in the range of 20-60 seconds.
0.1059 0.0795  SELECT DISTINCT resourceBucket FROM perch3_resources WHERE resourceAWOL=0 AND resourceType !=""
0.1582 0.0524  SELECT SQL_CALC_FOUND_ROWS DISTINCT r1.*, r2.resourceFile AS thumb, r2.resourceWidth AS thumbWidth, r2.resourceHeight AS thumbHeight, r2.resourceDensity AS thumbDensity FROM perch3_resources r1 LEFT OUTER JOIN perch3_resources r2 ON r2.resourceParentID=r1.resourceID AND r2.resourceKey='thumb' AND r2.resourceAWOL!=1 WHERE r1.resourceKey='orig' AND r1.resourceAWOL=0 AND r1.resourceBucket IN ('admin', 'banner', 'blog', 'charity', 'default', 'instagram', 'logo', 'meta', 'newsroom', 'portfolio', 'staff') ORDER BY r1.resourceUpdated DESC, r1.resourceID DESC LIMIT 0, 30
20.0008 19.8426  SELECT FOUND_ROWS() AS `count`
I have executed that query in isolation on the production server and the results were the same, taking 43 seconds to complete. This is happening on my local and production environments.
Here is the diagnostic:
Perch Runway: 3.0.11, PHP: 7.1.12-1+ubuntu16.04.1+deb.sury.org+1, MySQL: mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $, with PDO Server OS: Linux, fpm-fcgi Installed apps: content (3.0.11), assets (3.0.11), categories (3.0.11), root_analytics (2.0.0), perch_blog (5.6.1), root_heartbeat (1.0.1) App runtimes: <?php $apps_list = [ 'perch_blog', 'root_analytics' ]; PERCH_LOGINPATH: /admin PERCH_PATH: /home/forge/XXXXXX/public/admin PERCH_CORE: /home/forge/XXXXXX/public/admin/core PERCH_RESFILEPATH: /home/forge/XXXXXX/public/admin/resources Image manipulation: GD Imagick PHP limits: Max upload 100M, Max POST 100M, Memory: 512M, Total max file upload: 100M F1: 3b606135b33e6a102526838f4152a807 Resource folder writeable: Yes HTTP_HOST: XXXXXX DOCUMENT_ROOT: /home/forge/XXXXXX public REQUEST_URI: /admin/core/settings/diagnostics/ SCRIPT_NAME: /admin/core/settings/diagnostics/index.php
The client is a media company I would expect them to make heavy use of the assets area so this is quite a critical issue.
Edit: It seems that
SQL_CALC_FOUND_ROWS DISTINCT is the issue here. Removing it drops the query time to 150ms.