https://bugs.kde.org/show_bug.cgi?id=470117
Bug ID: 470117
Summary: Digikam query is slow on MariaDB while assigning
detected Person
Classification: Applications
Product: digikam
Version: 8.0.0
Platform: Ubuntu
OS: Linux
Status: REPORTED
Severity: normal
Priority: NOR
Component: Database-Mysql
Assignee: digikam-bugs-n...@kde.org
Reporter: tim...@timmib.de
Target Milestone: ---
SUMMARY
When i accept a bunch of face-suggestions to a Person the update process is
pretty slow. (~1s per Suggestion)
I detected two queries in mariaDB which might cause the problem.
SELECT DISTINCT
Images.id, Images.name, Images.album, Albums.albumRoot,
ImageInformation.rating, Images.category, ImageInformation.format,
ImageInformation.creationDate, Images.modificationDate, Images.fileSize,
ImageInformation.width, ImageInformation.height, ImageTagProperties.value,
ImageTagProperties.property, ImageTagProperties.tagid
FROM Images
INNER JOIN ImageTagProperties ON ImageTagProperties.imageid=Images.id
LEFT JOIN ImageInformation ON Images.id=ImageInformation.imageid
INNER JOIN Albums ON Albums.id=Images.album
WHERE Images.status=1 AND ( ( ( (ImageTagProperties.tagid=30 OR
ImageTagProperties.tagid IN (
SELECT id FROM TagsTree WHERE pid=30)) AND
ImageTagProperties.property='autodetectedPerson' ) OR (
(ImageTagProperties.tagid=30 OR ImageTagProperties.tagid IN (
SELECT id FROM TagsTree WHERE pid=30)) AND
ImageTagProperties.property='autodetectedFace' ) OR (
(ImageTagProperties.tagid=30 OR ImageTagProperties.tagid IN (
SELECT id FROM TagsTree WHERE pid=30)) AND
ImageTagProperties.property='ignoredFace' ) OR ( (ImageTagProperties.tagid=30
OR ImageTagProperties.tagid IN (
SELECT id FROM TagsTree WHERE pid=30)) AND
ImageTagProperties.property='tagRegion' ) ) ) LIMIT 10
and
SELECT tagid, COUNT(*) FROM ImageTagProperties LEFT JOIN Images ON
Images.id=ImageTagProperties.imageid WHERE
ImageTagProperties.property='autodetectedFace' AND Images.status=1 GROUP
BY tagid
STEPS TO REPRODUCE
1. Import 130k pictures
2. Detect faces
3. Recognize faces
4. Select 10-20 suggested faces
5. Wait for database to finish
OBSERVED RESULT
Slow processing of database update.
EXPECTED RESULT
10x faster processing, like locally on sqllite.
SOFTWARE/OS VERSIONS
ubuntu desktop and server 22.04
MariaDB 10.6
ADDITIONAL INFORMATION
--
You are receiving this mail because:
You are watching all bug changes.
