Sort memory needed for large galleries

[mawby]

Any advice on what I should set the sort memory to in order to prevent this error? Alternatively, could an index be added to avoid the sort altogether?

 

SELECT gallery_albums.*, author.* FROM `gallery_albums`  LEFT JOIN `core_members` AS `author` ON author.member_id = gallery_albums.album_owner_id WHERE gallery_albums.album_category_id=2 AND album_type<>4 AND ( gallery_albums.album_type IN(1,2) OR ( gallery_albums.album_type=3 AND ( gallery_albums.album_owner_id=1 OR gallery_albums.album_allowed_access IN (0,5,10) ) ) ) AND gallery_albums.album_hidden < 2 AND gallery_albums.album_hidden!=-2 AND gallery_albums.album_hidden !=-3 ORDER BY album_last_img_date desc LIMIT 2650,25
IPS\Db\Exception: Out of sort memory, consider increasing server sort buffer size (1038)
#0 /var/www/html/system/Db/Select.php(441): IPS\Db\_Select->runQuery()
#1 [internal function]: IPS\Db\_Select->rewind()
#2 [internal function]: IteratorIterator->rewind()
#3 /var/www/html/system/Helpers/Table/Content.php(343): iterator_to_array()
#4 /var/www/html/system/Helpers/Table/Table.php(489): IPS\Helpers\Table\_Content->getRows()
#5 /var/www/html/applications/gallery/modules/front/gallery/browse.php(213): IPS\Helpers\Table\_Table->__toString()
#6 /var/www/html/applications/gallery/modules/front/gallery/browse.php(115): IPS\gallery\modules\front\gallery\_browse->_category()
#7 /var/www/html/system/Dispatcher/Controller.php(101): IPS\gallery\modules\front\gallery\_browse->manage()
#8 /var/www/html/system/Content/Controller.php(50): IPS\Dispatcher\_Controller->execute()
#9 /var/www/html/applications/gallery/modules/front/gallery/browse.php(49): IPS\Content\_Controller->execute()
#10 /var/www/html/system/Dispatcher/Dispatcher.php(152): IPS\gallery\modules\front\gallery\_browse->execute()
#11 /var/www/html/index.php(13): IPS\_Dispatcher->run()
#12 {main}

[bfarber]

Paginating through large record sets is not always super efficient in MySQL.

The default value for sort_buffer_size is 262144. If you're using anything less than that, I would recommend starting there.

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sort_buffer_size

[mawby]

Just checked and the default is being used. I changed the SQL slightly, so it will used the index instead of doing a table scan, and that's fixed the problem. It would require remembering the album_id of the previous last row returned though.

SELECT gallery_albums.*, author.*
FROM `gallery_albums`
LEFT JOIN `core_members` AS `author`
ON author.member_id = gallery_albums.album_owner_id 
WHERE gallery_albums.album_id > 2650
AND gallery_albums.album_category_id=2 
AND album_type<>4 
AND ( gallery_albums.album_type IN(1,2)
    OR ( gallery_albums.album_type=3 
    AND ( gallery_albums.album_owner_id=1 
        OR gallery_albums.album_allowed_access IN (0,5,10) ) ) ) 
AND gallery_albums.album_hidden < 2 
AND gallery_albums.album_hidden !=-2 
AND gallery_albums.album_hidden !=-3 
ORDER BY album_last_img_date DESC 
LIMIT 25 

Edited September 18, 2020 by mawby

[mawby]

Of course, it wouldn't actually help in this situation where I was on page one and clicked the last page button.

[mawby]

Got it working with a value of 1048576.