adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-10-30 21:52:00 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

storage usage broken with big galleries #1165

New Issue
Closed
opened 2026-10-05 00:33:57 +02:00 by adam · 1 comment
adam commented 2026-10-05 00:33:57 +02:00
Owner
Copy link
Copy source

If your photo gallery contains 1 million photos, the current (11.3) algorithm to calculate the storage space used by each kind of files (photos/videos/formats/other) will reach memory limit.

Furthermore, there is no need to calculate the sum in the PHP. The database can do it, more efficiently.

On my test database with 960k photos, it takes 800ms to fetch all individuals photos and 4000ms to generate the whole page. With an aggregation query, the SQL takes 1700ms (that's longer) but the page generates in 3000ms (that's faster). Here is the aggregation query I used to test:

$query = '
SELECT
   SUBSTRING_INDEX(path,".",-1) AS ext,
   SUM(filesize) AS filesize
  FROM `'.IMAGES_TABLE.'`
  GROUP BY ext
;';

$file_extensions = query2array($query, 'ext', 'filesize');

Even optimized, it takes too much time in my opinion. Next step would be to cache this.

If your photo gallery contains 1 million photos, the current (11.3) algorithm to calculate the storage space used by each kind of files (photos/videos/formats/other) will reach memory limit. Furthermore, there is no need to calculate the sum in the PHP. The database can do it, more efficiently. On my test database with 960k photos, it takes 800ms to fetch all individuals photos and 4000ms to generate the whole page. With an aggregation query, the SQL takes 1700ms (that's longer) but the page generates in 3000ms (that's faster). Here is the aggregation query I used to test: ``` $query = ' SELECT SUBSTRING_INDEX(path,".",-1) AS ext, SUM(filesize) AS filesize FROM `'.IMAGES_TABLE.'` GROUP BY ext ;'; $file_extensions = query2array($query, 'ext', 'filesize'); ``` Even optimized, it takes too much time in my opinion. Next step would be to cache this. <!-- github-sync-id:808627955 -->
adam closed this issue 2026-10-05 00:33:57 +02:00
adam commented 2026-10-05 00:33:58 +02:00
Author
Owner
Copy link
Copy source

Fixed in 370ba45 on master and eab9d44 on 11.x

Fixed in 370ba45 on master and eab9d44 on 11.x <!-- github-comment-id:791308268 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#1165