Guest user causes entire image list to be loaded

[Undergrid]

I originally posted (parts of) the following on the official Piwigo forums when I thought it was a core issue, but it turns out it's in SmartAlbums.

So I've been working on optimising my 166 thousand image piwigo gallery and I noticed that guest users were loading pages a lot slower than my admin user.

For example, the admin user loads category 1 with a (chrome reported) wait time of 141ms. The guest user takes between 3.95 seconds and over 10 seconds.

So I looked at the queries and found the following:

For the administrator:

SELECT *
  FROM piwigo_categories
  WHERE id = 1
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.017 s)
(num rows        : 1 )

[11] 
SELECT DISTINCT(image_id)
  FROM piwigo_image_category
    INNER JOIN piwigo_images ON id = image_id
  WHERE
    category_id = 1

  ORDER BY file ASC, id ASC
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.017 s)
(num rows        : 0 )

[12] 
SELECT DISTINCT category_id
  FROM piwigo_category_filters
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.017 s)
(num rows        : 1 )

[13] 
SELECT pt.id, pt.visible, pt.nfo, ptl.lang, ptl.title, ptl.content
          FROM piwigo_amm_personalised pt
            LEFT JOIN piwigo_amm_personalised_langs ptl
            ON pt.id=ptl.id
          WHERE (ptl.lang = '*' OR ptl.lang = 'en_GB')  AND ptl.content != ''  ORDER BY pt.id, ptl.lang ASC 
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.017 s)
(num rows        : 0 )

And for the guest:

[12] 
SELECT *
  FROM piwigo_categories
  WHERE id = 1
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.031 s)
(num rows        : 1 )

[12] 
SELECT DISTINCT(image_id)
  FROM piwigo_image_category
    INNER JOIN piwigo_images ON id = image_id
  WHERE
    category_id = 1
AND (category_id NOT IN (1694,675,676,677,678,1340,1373,1363,1361,1365,1367,1343,1347,1345,1359,1379,1383,1355,1375,1357,1377,1349,1381,1371,1353,1351,1369,3090,3057,3058,3073,3074,5209,2846))
  ORDER BY name ASC,file ASC, id ASC
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.031 s)
(num rows        : 0 )

[13] 
SELECT DISTINCT category_id
  FROM piwigo_category_filters
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.031 s)
(num rows        : 1 )

[14] 
SELECT id
  FROM piwigo_categories
  WHERE
    (id NOT IN (1694,675,676,677,678,1340,1373,1363,1361,1365,1367,1343,1347,1345,1359,1379,1383,1355,1375,1357,1377,1349,1381,1371,1353,1351,1369,3090,3057,3058,3073,3074,5209,2846,5234))
(this query time : 0.000 s)
(total SQL time  : 0.002 s)
(total time      : 0.032 s)
(num rows        : 3771 )

[15] 
SELECT DISTINCT(image_id)
  FROM piwigo_image_category
    INNER JOIN piwigo_images ON id = image_id
  WHERE
    category_id IN (1,3, ...)

  ORDER BY name ASC,file ASC, id ASC
;
(this query time : 10.852 s)
(total SQL time  : 10.853 s)
(total time      : 10.884 s)
(num rows        : 168736 )

[16] 
SELECT pt.id, pt.visible, pt.nfo, ptl.lang, ptl.title, ptl.content
          FROM piwigo_amm_personalised pt
            LEFT JOIN piwigo_amm_personalised_langs ptl
            ON pt.id=ptl.id
          WHERE (ptl.lang = '*' OR ptl.lang = 'en_GB')  AND ptl.content != ''  ORDER BY pt.id, ptl.lang ASC 
(this query time : 0.000 s)
(total SQL time  : 10.854 s)
(total time      : 10.947 s)
(num rows        : 0 )

Two things to note about the above:

1) I removed a huge list if category numbers in query 15 for the guest for clarity 2) Yes, there actually are two number 11 queries for the admin and two number 12 queries for the guest listed on the generated web pages.

Note that query 15 ends up selecting every image in the database and then, just to make things worse, sorts them on fields that aren't indexed in the database

So the administrator seems to skip queries 14 and 15 (from the guest list) no apparent ill effect on the web page, but the guest account basically thrashes the database.

Obviously query 15 is the one at the bottom of events.inc.php. As it stands, this is a hugely expensive operation on larger databases. Is there any reason this couldn't be limited to the current category and it's children (if in flat mode)? Indeed, if $pages['items'] is already populated, couldn't the query be omitted totally and just remove the images from the list the user isn't supposed to see?