Jump to content

Sort memory needed for large galleries


mawby
 Share

Recommended Posts

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}

Link to comment
Share on other sites

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 by mawby
Link to comment
Share on other sites

 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...