Performance Issue Administrator Gallery Images
Posted: 08 Apr 2015, 19:45
Hello Jan,
I do not want to bother you, but I have to ask new again because from time to time the admin site gets killed by my hoster for the long execution duration.
I am using phoca gallery 4.1.2 with now a litte over 100.000 pictures...
I have debugged this a little and its because of the mysql queries.
I have attaced a Saved Version of a view of my admin page (including all) while Joomla Debug was enabled to give you all the DB Queries and their runtime. https://www.hmayer.net/index.php/dateig ... erformance (No attachments here
)
As you can see there are two queries specifically I am pointing to - Duration > 7seconds each.
My Questions for optimizations would be
- Is there a way to not Query all pictures (113650 rows) as in Query #8
- Is this query identical to query #9 except of the "LIMIT" 0,20 (Paging only here?)
- Query #9 is not significantly faster because of the hugh number of rows in the temp operation
- I would recommend pushing the limit and WHERE further into the query - as SUBQUERY for phocagallery
- this is save because there are only left joins
- and those will then not operate on ALL rows, but only on the "LIMIT" (20) rows we selected
- In my case a query speed difference from 7.6 seconds to 0.3 seconds.
The original Query
and the proposed change.
Anything possible there?
Hannes
PS: Also on the gallery frontend there are a lot of queries which are executed twice?
I do not want to bother you, but I have to ask new again because from time to time the admin site gets killed by my hoster for the long execution duration.
I am using phoca gallery 4.1.2 with now a litte over 100.000 pictures...
I have debugged this a little and its because of the mysql queries.
I have attaced a Saved Version of a view of my admin page (including all) while Joomla Debug was enabled to give you all the DB Queries and their runtime. https://www.hmayer.net/index.php/dateig ... erformance (No attachments here
)As you can see there are two queries specifically I am pointing to - Duration > 7seconds each.
My Questions for optimizations would be
- Is there a way to not Query all pictures (113650 rows) as in Query #8
- Is this query identical to query #9 except of the "LIMIT" 0,20 (Paging only here?)
- Query #9 is not significantly faster because of the hugh number of rows in the temp operation
- I would recommend pushing the limit and WHERE further into the query - as SUBQUERY for phocagallery
- this is save because there are only left joins
- and those will then not operate on ALL rows, but only on the "LIMIT" (20) rows we selected
- In my case a query speed difference from 7.6 seconds to 0.3 seconds.
The original Query
Code: Select all
SELECT
a.*,
...
FROM
`j25_phocagallery` AS a
LEFT JOIN
`j25_languages` AS l ON l.lang_code = a.language
LEFT JOIN
j25_users AS uc ON uc.id = a.checked_out
LEFT JOIN
j25_users AS uua ON uua.id = a.userid
LEFT JOIN
j25_phocagallery_categories AS c ON c.id = a.catid
LEFT JOIN
j25_users AS ua ON ua.id = c.owner_id
LEFT JOIN
j25_phocagallery_img_votes_statistics AS v ON v.imgid = a.id
WHERE
(a.published IN (0 , 1))
GROUP BY a.id
ORDER BY a.title ASC
LIMIT 0 , 20Code: Select all
SELECT
a.*,
...
FROM
(SELECT
*
FROM
`j25_phocagallery`
WHERE
published IN (0 , 1)
ORDER BY title ASC
LIMIT 0 , 20) AS a
LEFT JOIN
`j25_languages` AS l ON l.lang_code = a.language
LEFT JOIN
j25_users AS uc ON uc.id = a.checked_out
LEFT JOIN
j25_users AS uua ON uua.id = a.userid
LEFT JOIN
j25_phocagallery_categories AS c ON c.id = a.catid
LEFT JOIN
j25_users AS ua ON ua.id = c.owner_id
LEFT JOIN
j25_phocagallery_img_votes_statistics AS v ON v.imgid = a.id
GROUP BY a.id
ORDER BY a.title ASC
Hannes
PS: Also on the gallery frontend there are a lot of queries which are executed twice?
