Long query times viewing assets app

Hi again,

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  [7] SELECT DISTINCT resourceBucket FROM perch3_resources WHERE resourceAWOL=0 AND resourceType !=""

0.1582  0.0524  [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 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 [4686] 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.

Thanks.

Edit: It seems that SQL_CALC_FOUND_ROWS DISTINCT is the issue here. Removing it drops the query time to 150ms.