519125 2026-04-18 23:24:07 +0000 Improve face tag query performance: CREATE INDEX idx_itp_tagid_property ON ImageTagProperties(tagid, property(50)); 2026-04-25 16:34:16 +0000 1 1 2 Applications digikam Database-Schema 9.0.0 Other Linux RESOLVED FIXED NOR normal --- 0 zizka digikam-bugs-null caulier.gilles metzpinguin michael_miller https://invent.kde.org/graphics/digikam/-/commit/3b256067d8938838522c16ab7df05fe5c03f21c2 9.1.0 0 oldest_to_newest 2510731 0 zizka 2026-04-18 23:24:07 +0000 DigiKam's default database schema should include a composite index on `ImageTagProperties(tagid, property)` to significantly improve the performance of face clustering queries. Background: ----------- Current performance issue (related to separate bug report "Face tag queries slow due to inefficient OR+subquery pattern"): - Face tag queries in the clustering UI take 10-12 seconds on a database with ~400K images - The cause is a full-table scan of ImageTagProperties (340K rows) instead of using available indexes - MariaDB has 6 indexes on ImageTagProperties but none on the (tagid, property) pair Proposed Solution: ------------------ Add a composite index to the DigiKam database schema: CREATE INDEX idx_itp_tagid_property ON ImageTagProperties(tagid, property(50)); Why this helps: - Queries that filter by both `tagid` AND `property` can use the index for both conditions - While the main face clustering query (with OR+subquery) cannot use this index due to its structure, simpler DigiKam queries that filter directly by these columns will benefit significantly - Post-fix statistics (ANALYZE TABLE) show 25-33x speedup for other affected queries (10-12s -> 0.4s) Performance Impact: ------------------- - Adds ~1 MB to database size (negligible) - Minimal write overhead (index updates on INSERT/DELETE to ImageTagProperties) - Significant read improvement for face tag filtering queries - No negative impact on existing queries (MariaDB optimizer will skip the index if not useful) Implementation: --------------- This index should be added to: 1. **Database schema upgrade script** (e.g., db_schema_XXX.sql) for new installations 2. **Migration script** for existing databases (if DigiKam version supports automatic schema upgrades) 3. **Database initialization code** in the core (if applicable) SQL to add to schema: CREATE INDEX idx_itp_tagid_property ON ImageTagProperties(tagid, property(50)); Why `property(50)` instead of full column: - ImageTagProperties.property is a TEXT column (unlimited length) - MariaDB limits composite index size to 3072 bytes - The longest actual DigiKam property value is ~19 characters (e.g., 'faceTagExtendedData') - A 50-character prefix covers all actual values fully while keeping the index compact Related Issues: --------------- - Separate bug report: "Face tag queries slow due to inefficient OR+subquery pattern" (addresses the root cause of slow face queries) Testing: -------- This change is safe and transparent: - Run: ANALYZE TABLE ImageTagProperties; (updates optimizer statistics) - Existing queries continue to work unchanged - New queries automatically benefit from the index when applicable System Info: ============ - Tested on: MariaDB 12.3.1, Ubuntu Linux, 24 cores, 50 GB RAM - Database size: ~900 MB - Number of ImageTagProperties rows: ~360K - Benefit observed: 25-33x speedup for tag-property filtered queries Rationale: ========== While the root cause of the main face clustering slowness (OR+subquery pattern) requires changes to the query generator, adding this index is a simple, low-risk improvement that: 1. Benefits multiple DigiKam queries immediately 2. Requires no code changes (SQL-only schema addition) 3. Has no negative side effects 4. Should be included as a standard part of the database schema 2510747 1 zizka 2026-04-19 02:02:32 +0000 I've attempted on implementing this, but with uncertain result... Feel free to discard if it's more of a noise than a useful change: https://invent.kde.org/graphics/digikam/-/merge_requests/395 2510751 2 zizka 2026-04-19 02:10:29 +0000 That said, I have to add: I have added this index on my local DigiKam db instance, and it helped as expected - the queries went from ~15s to 1-2 seconds. BTW: Could DigiKam perhaps cache the "special" tags like "People"? One query in particular has a sub-query about "tag #7" and this makes the query optimizer helpless and leads to some scanning. If DigiKam did this sub-query once (reasonably assuming these special tags never change the ID), the following would be a lot faster: ... WHERE Images.status=1 AND ( ( (ImageTagProperties.tagid=7 OR ImageTagProperties.tagid IN (SELECT id FROM TagsTree WHERE pid=7)) AND ImageTagProperties.property IN ('autodetectedPerson','autodetectedFace','ignoredFace','tagRegion') ) ); ---> WHERE Images.status=1 AND ( ImageTagProperties.tagid IN (7, ...) AND ImageTagProperties.property IN ('autodetectedPerson','autodetectedFace','ignoredFace','tagRegion') ); 2510777 3 metzpinguin 2026-04-19 04:45:25 +0000 *** This bug has been marked as a duplicate of bug 514970 *** 2510785 4 metzpinguin 2026-04-19 06:41:13 +0000 Git commit ad5bb7d1da29dc6d90e4961d53afe79bbfa429b3 by Maik Qualmann. Committed on 19/04/2026 at 06:40. Pushed by mqualmann into branch 'master'. prepare database update to add ImageTagProperties index and a timezone field Related: bug 514970, bug 517889, bug 510261, bug 495506, bug 491608, bug 485836, bug 251357 M +46 -1 core/data/database/dbconfig.xml.cmake.in M +8 -0 core/libs/database/coredb/coredbschemaupdater.cpp https://invent.kde.org/graphics/digikam/-/commit/ad5bb7d1da29dc6d90e4961d53afe79bbfa429b3 2512100 5 metzpinguin 2026-04-25 14:41:23 +0000 Git commit 603698d3e1659a553fc4b79cc84c5b29f8fcbca7 by Maik Qualmann. Committed on 25/04/2026 at 14:38. Pushed by mqualmann into branch 'master'. prepare database update to add Indexes and timezone field Related: bug 514970, bug 519389, bug 519390 M +45 -13 core/data/database/dbconfig.xml.cmake.in https://invent.kde.org/graphics/digikam/-/commit/603698d3e1659a553fc4b79cc84c5b29f8fcbca7 2512108 6 metzpinguin 2026-04-25 16:34:16 +0000 Git commit 3b256067d8938838522c16ab7df05fe5c03f21c2 by Maik Qualmann. Committed on 25/04/2026 at 16:33. Pushed by mqualmann into branch 'master'. database update to add Indexes and timezone field to V17 Related: bug 514970, bug 519389, bug 519390 FIXED-IN: 9.1.0 M +4 -4 NEWS M +8 -9 core/data/database/dbconfig.xml.cmake.in M +1 -1 core/libs/database/coredb/coredbschemaupdater.cpp https://invent.kde.org/graphics/digikam/-/commit/3b256067d8938838522c16ab7df05fe5c03f21c2