https://bugs.kde.org/show_bug.cgi?id=391115
Bug ID: 391115
Summary: Very slow search, full text search (FTS) proposition
for huge speedup.
Product: digikam
Version: unspecified
Platform: Other
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: NOR
Component: Database
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: ---
Hi.
I have more than 100000 (100k+) pictures in my DigiKam library.
DigiKam seems to be VERY SLOW with such amount of pictures.
Using Sqlite database on RamDisk or using MySql database doesn't realy help
when I try to filter (search) pictures.
I notice that the speed issue is caused, by inefficent queries.
I cought SQL queris which looks like:
SELECT * from .. WHERE some_key LIKE '%query%' (but ofcourse query is done
across multiple tables)
Queries to database with 'like' operator are very SLOW BY DESIGN.
Such queries requires FULL TABLE SCAN (reading every record and comparing it
with a query).
Nowadays many database backends support builtin full text search engines.
Even Sqlite have such one!
========
Sqlite
========
-----
EXAMPLE SPEED GAIN by using FTS index instead of 'like' operator.
from: https://www.sqlite.org/fts3.html
-----
SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux'; /* 0.03 seconds
*/
SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds
*/
http://www.sqlitetutorial.net/sqlite-full-text-search/
https://sqlite.org/fts5.html
=========
PostgreSQL
=========
https://www.postgresql.org/docs/9.5/static/textsearch.html
=========
MySQL
=========
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html
I belive introducing Full text search to DigiKam, could drasticly improve
search/filtering performace.
Without this feature, this application is useless.
Queries TAKES MINUTES (on i7, 16GB ram, SSD).
Moreover it's not clear for the user if DigiKam is realy doing anything during
search. After minutes results appear.
Later scrollig is as fast as usually, the problem is with searchin/filtering.
--
You are receiving this mail because:
You are watching all bug changes.
