519390 2026-04-25 05:54:05 +0000 DB: ImageTagProperties.property should be VARCHAR, not TEXT -- TEXT prevents histogram stats and forces wrong query plans 2026-05-01 16:18:47 +0000 1 1 2 Applications digikam Database-Schema 9.0.0 Other Linux RESOLVED FIXED NOR wishlist --- 0 zizka digikam-bugs-null caulier.gilles metzpinguin https://invent.kde.org/graphics/digikam/-/commit/3b256067d8938838522c16ab7df05fe5c03f21c2 9.1.0 0 oldest_to_newest 2512045 0 zizka 2026-04-25 05:54:05 +0000 The `property` column on `ImageTagProperties` is declared as `TEXT`, which causes two problems. **Problem A -- no histogram possible.** MariaDB/MySQL cannot collect statistics on TEXT/BLOB columns. Without a histogram the optimizer has to assume all property values are equally common. In practice the distribution is very skewed: | value | rows | % of table | |---------------------|---------|------------| | autodetectedFace | 580 123 | 69 % | | autodetectedPerson | 116 638 | 14 % | | tagRegion | 53 505 | 6 % | | faceToTrain | 52 670 | 6 % | | faceTagExtendedData | 32 960 | 4 % | | ignoredFace | 5 751 | 0.7 % | So the optimizer never trusts a range scan on `property = 'autodetectedFace'` and falls back to a full table scan every time. **Problem B -- existing index has the wrong column order.** The current index is `(tagid, property)`. Queries that filter on `property` first -- which is the normal case for the People panel -- cannot use it. With `property` as TEXT the index also requires a prefix `property(50)`, making cardinality estimates even less accurate. The relevant query from the slow-query log: /---code sql SELECT tagid, COUNT(*) FROM ImageTagProperties LEFT JOIN Images ON Images.id = ImageTagProperties.imageid WHERE ImageTagProperties.property = 'autodetectedFace' AND Images.status = 1 GROUP BY tagid; \--- `EXPLAIN`: `type = ALL, 841 k rows, Using temporary; Using filesort`. Fired about 146 times/day for `autodetectedFace` alone (381 s total), plus more for each other value. **Fix -- three steps:** /---code sql -- 1. Change column type so histograms and full-length index keys become possible. -- All existing values fit easily in VARCHAR(255); this is transparent to the app. ALTER TABLE ImageTagProperties MODIFY property VARCHAR(255); -- 2. Add a covering index with property as the leading column. -- tagid second means GROUP BY tagid needs no filesort. -- imageid third makes it a covering index (no heap read on ITP at all). CREATE INDEX idx_itp_property_tagid_img ON ImageTagProperties (property, tagid, imageid); -- 3. Now that property is VARCHAR, build a histogram. ANALYZE TABLE ImageTagProperties PERSISTENT FOR COLUMNS (property) INDEXES ALL; \--- After this the optimizer can see that `'autodetectedFace'` is 69 % of the table and weigh a range scan accordingly, and it will definitely choose the index for `'ignoredFace'` (0.7 %) and `'tagRegion'` (6 %). **Bonus: two redundant indexes can be removed.** The table currently has 5 indexes. Two are strict prefixes of others and serve no purpose: /---code sql -- (imageid) is a prefix of (imageid, tagid) -- redundant DROP INDEX imagetagproperties_imageid_index ON ImageTagProperties; -- (tagid) is a prefix of (tagid, property) -- redundant DROP INDEX imagetagproperties_tagid_index ON ImageTagProperties; \--- Removing them reduces write amplification during face detection runs, where DigiKam inserts into this table at high volume. 2512046 1 zizka 2026-04-25 06:09:47 +0000 I did these tweaks and the UI performance issues almost completely disappeared. Things that took 15 seconds are now instant. I highly recommend prioritizing this for 9.1. 2512050 2 caulier.gilles 2026-04-25 06:22:57 +0000 Thanks Ondrej for this kind of report and optimization proposal. It's very appreciate. I suppose that you deal with this entry with the Mysql/MariaDB databases. What's about Sqlite ? 2512097 3 metzpinguin 2026-04-25 14:41:07 +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 519125, bug 514970, bug 519389 M +45 -13 core/data/database/dbconfig.xml.cmake.in https://invent.kde.org/graphics/digikam/-/commit/603698d3e1659a553fc4b79cc84c5b29f8fcbca7 2512109 4 metzpinguin 2026-04-25 16:34:24 +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 519125, bug 514970, bug 519389 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 2513203 5 zizka 2026-05-01 16:18:47 +0000 In case this is useful info: I've tried to run DigiKam 9.1 build from 2026-04-30 (appimage) against my database with these changes (done by me previously). I did not additional migration myself. All works.