519389 2026-04-25 05:39:13 +0000 MariaDB/MySQL: missing index on Images.status causes full table scan on tag count, calendar, and People panel queries 2026-05-01 16:15:33 +0000 1 1 2 Applications digikam Database-Schema 9.0.0 Other Linux RESOLVED FIXED NOR normal --- 0 zizka digikam-bugs-null caulier.gilles https://invent.kde.org/graphics/digikam/-/commit/3b256067d8938838522c16ab7df05fe5c03f21c2 9.1.0 0 oldest_to_newest 2512044 0 zizka 2026-04-25 05:39:13 +0000 Tested on MariaDB with a collection of ~711 k images. The `Images` table has no index on the `status` column, so every query that filters `WHERE Images.status = 1` does a full scan of the ~174 MB clustered heap. Three very common queries are affected: /---code sql -- tag count refresh (People panel sidebar) SELECT tagid, COUNT(*) FROM ImageTags LEFT JOIN Images ON Images.id = ImageTags.imageid WHERE Images.status = 1 GROUP BY tagid; -- calendar date histogram SELECT creationDate, COUNT(*) FROM ImageInformation INNER JOIN Images ON Images.id = ImageInformation.imageid WHERE Images.status = 1 GROUP BY creationDate; -- people panel: load images for one person tag SELECT DISTINCT Images.id, ... FROM Images INNER JOIN ImageTagProperties ON ImageTagProperties.imageid = Images.id WHERE Images.status = 1 AND ...; \--- From the slow-query log over one day (threshold 1 s): | Query | Calls | Total | Avg | |--------------------------|-------|--------|------| | tag count refresh | 368 | 753 s | 2.1s | | calendar date histogram | 115 | 576 s | 5.0s | | people panel per-person | 197 | 397 s | 2.0s | `EXPLAIN` confirms `type = ALL` on `Images` for all three. The fix is one line: /---code sql CREATE INDEX idx_images_status ON Images (status); ANALYZE TABLE Images; \--- After adding it, `Images` switches to `type = ref, Using index` for the histogram query (615 k per-row lookups now hit only the small secondary index instead of the full heap row) and to a compact index scan for the tag count query. No data migration, no application change, safe with `ALGORITHM=INPLACE`. `status` has only two values (1 = normal, 3 = trashed), so the index is admittedly low selectivity. But even at 98 % match the secondary index is a small fraction of the 174 MB clustered table, meaning the engine touches far fewer buffer pool pages per query. 2512047 1 zizka 2026-04-25 06:11:07 +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. 2512048 2 caulier.gilles 2026-04-25 06:20:44 +0000 Can you confirm with 9.1.0 pre-release ? And what's about sqlite database ? 2512049 3 zizka 2026-04-25 06:21:00 +0000 (That said, IMHO it would still be better to detach the UI from the SQL queries and make it completely asynchronous, and 'cheating' a bit when loading stuff - not everything needs to be computed for the entire collection, the UI will be completely fine loading just the first 200 items, on whichever page. Doesn't even need to be consistent for the first 5 seconds, so most often needed data can be cached and re-read eventually and applied to the UI.) 2512051 4 zizka 2026-04-25 06:23:54 +0000 > Can you confirm with 9.1.0 pre-release ? Confirm what exactly? What is the status in 9.1? Are the indexes already there? Or shall I confirm that the indexes are applicable this way? Regarding SQLite, IDK what's it's schema capabilities, IIRC it doesn't have the VARCHAR(limit) syntax, right? So I guess the schema would need to differ conditionally, if that is applicable. I hope it is. 2512052 5 zizka 2026-04-25 06:25:07 +0000 (Sorry, mixing the two index-related tickets together - the VARCHAR(limit) note is for https://bugs.kde.org/show_bug.cgi?id=519390 ). 2512053 6 caulier.gilles 2026-04-25 06:26:25 +0000 With 9.1.0, Maik has already started few days ago to patch the database schema. So it will be useful to double check in a few days if this problem is reproducible with the 9.1.0 pre-release available here : https://files.kde.org/digikam/ 2512054 7 caulier.gilles 2026-04-25 06:30:50 +0000 The Sqlite and Mysql/MariaDB schema rules are managed differently than the backend. You will find the code here : - Core database schema updater : https://invent.kde.org/graphics/digikam/-/blob/master/core/libs/database/coredb/coredbschemaupdater.cpp?ref_type=heads Note: we have the same kind of classes for all other database (similarity, faces, thumbnails, etc...) - Database SQL rules: https://invent.kde.org/graphics/digikam/-/blob/master/core/data/database/dbconfig.xml.cmake.in?ref_type=heads Gilles Caulier 2512099 8 metzpinguin 2026-04-25 14:41:15 +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 519390 M +45 -13 core/data/database/dbconfig.xml.cmake.in https://invent.kde.org/graphics/digikam/-/commit/603698d3e1659a553fc4b79cc84c5b29f8fcbca7 2512107 9 metzpinguin 2026-04-25 16:34:07 +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 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 2513202 10 zizka 2026-05-01 16:15:33 +0000 I'm (belatedly) confirming that these change works with DigiKam 9.1 nightly build from 2026-04-30.