mirror of
https://github.com/Piwigo/Piwigo.git
synced 2026-10-30 21:52:00 +01:00
optimize data load for huge number of sub-albums #1731
Assignees
adam (Adam Wojcieszonek)
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: adam/Piwigo#1731
Reference in new issue
Block a user
If you have many (thousands) sub-albums at a given level, Piwigo will perform very slow SQL queries, even if only 12 albums (by default) are displayed on the page.
hi
@plegall i test
SELECT
c.*,
user_representative_picture_id,
nb_images,
date_last,
max_date_last,
count_images,
nb_categories,
count_categories
FROM piwigo_categories c
INNER JOIN piwigo_user_cache_categories ucc
ON id = cat_id
AND user_id = 1
WHERE id_uppercat is NULL
ORDER BY
rank(this query time : 0.000 s)
(total SQL time : 0.020 s)
(total time : 0.087 s)
(num rows : 6 )
[16]
SELECT *
FROM piwigo_images
WHERE id IN (60)
;
(this query time : 0.001 s)
(total SQL time : 0.020 s)
(total time : 0.088 s)
(num rows : 1 )
[0.089 s, 16 queries]
test 2
SELECT
c.*,
user_representative_picture_id,
nb_images,
date_last,
max_date_last,
count_images,
nb_categories,
count_categories
FROM piwigo_categories c
INNER JOIN piwigo_user_cache_categories ucc
ON id = cat_id
AND user_id = 1
WHERE id_uppercat is NULL
ORDER BY
rank(this query time : 0.003 s)
(total SQL time : 0.263 s)
(total time : 0.332 s)
(num rows : 40 )
[14]
SELECT
category_id,
MIN(date_creation) AS
from,MAX(date_creation) AS
toFROM piwigo_image_category
INNER JOIN piwigo_images ON image_id = id
WHERE category_id IN (189,1,8,39,41,42,43,120,1026,1116,1194,1272,1309,1310,1432,1434,1435,1436,1492,1493,1591,1592,1593,1594,1595,1596,1597,3799,4239,4244,4302,4342,4343,4344,4346)
GROUP BY category_id
;
(this query time : 0.109 s)
(total SQL time : 0.373 s)
(total time : 0.442 s)
(num rows : 30 )
[15]
SELECT *
FROM piwigo_images
WHERE id IN (5523,50,2412,310,322,324,622,2347,10406,15247,21410,16448,15136,15137,16085,16079,16081,16083,17385,17394,16048,16046,16047,16039,16045,16043,16036,18825,10349,22989,10625,10295,10284,10312,11323)
;
(this query time : 0.012 s)
(total SQL time : 0.385 s)
(total time : 0.454 s)
(num rows : 35 )
[21.119 s, 15 queries]
test 3
SELECT
c.*,
user_representative_picture_id,
nb_images,
date_last,
max_date_last,
count_images,
nb_categories,
count_categories
FROM piwigo_categories c
INNER JOIN piwigo_user_cache_categories ucc
ON id = cat_id
AND user_id = 1
WHERE id_uppercat is NULL
ORDER BY
rank(this query time : 0.002 s)
(total SQL time : 0.009 s)
(total time : 0.020 s)
(num rows : 2 )
[17]
SELECT
category_id,
MIN(date_creation) AS
from,MAX(date_creation) AS
toFROM piwigo_image_category
INNER JOIN piwigo_images ON image_id = id
WHERE category_id IN (14,13)
GROUP BY category_id
;
(this query time : 0.001 s)
(total SQL time : 0.010 s)
(total time : 0.021 s)
(num rows : 2 )
[18]
SELECT *
FROM piwigo_images
WHERE id IN (89,87)
;
(this query time : 0.000 s)
(total SQL time : 0.010 s)
(total time : 0.022 s)
(num rows : 2 )
[0.022 s, 18 queries]
@klaoun how many photos? how many albums? how many root albums?
test 1 1 photo 6 album 1 root albums
test 2 22903 photos 4310 albums 12 roots albums
test 3 2 photos 5 albums 1 root album
in my test 2 in sub album 246 sub album 124 photos 6865 images
SELECT
c.*,
user_representative_picture_id,
nb_images,
date_last,
max_date_last,
count_images,
nb_categories,
count_categories
FROM piwigo_categories c
INNER JOIN piwigo_user_cache_categories ucc
ON id = cat_id
AND user_id = 1
WHERE id_uppercat = 189
ORDER BY
rank(this query time : 0.010 s)
(total SQL time : 0.401 s)
(total time : 0.613 s)
(num rows : 246 )
can you elaborate on what you want to show with these tests?
Maybe there is a limit. But why do you have 0.887 in this query time it should turn out below 0.1
I'm looking at the limits of piwigo.
Currently the album limit is 65534 id and the image id limit is 16 million
Of course there is a technical limit for the number of albums or photos. The limit is so high that it should not be a problem before many decades.
My problem here is that some SQL queries takes too much time and I want to reduce it. In this example, Piwigo fetches potentially thousands of albums but will only display a single page of 12 (by default) albums. This is wasted time. It should be improved.