[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=391115

--- Comment #11 from caulier.gil...@gmail.com ---
@Michał

digiKam 8.0.0 is out. This file still valid ?

Best regards

Gilles Caulier

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[Michał Karwowski]

https://bugs.kde.org/show_bug.cgi?id=391115

--- Comment #10 from Michał Karwowski  ---
Hi, nice that you are writing.

Actually, I'm unable to check it even in next 4 weeks :/
For so many reasons, that it's even hard to explain.
I believe that from mid-February i will have resources, a bit of time etc

/Michał Karwowski


pon., 10 sty 2022 o 09:11  napisał(a):

> https://bugs.kde.org/show_bug.cgi?id=391115
>
> --- Comment #9 from caulier.gil...@gmail.com ---
> Hi Michal and happy new year,
>
> Can you check if problem remain with digiKam 7.5.0 pre-release bundle
> available
> here :
>
> https://files.kde.org/digikam/
>
> Thanks in advance
>
> Gilles Caulier
>
> --
> You are receiving this mail because:
> You reported the bug.

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=391115

--- Comment #9 from caulier.gil...@gmail.com ---
Hi Michal and happy new year,

Can you check if problem remain with digiKam 7.5.0 pre-release bundle available
here :

https://files.kde.org/digikam/

Thanks in advance

Gilles Caulier

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=391115

--- Comment #8 from caulier.gil...@gmail.com ---
digiKam 7.0.0 stable release is now published:

https://www.digikam.org/news/2020-07-19-7.0.0_release_announcement/

We need a fresh feedback on this file using this version.

Best Regards

Gilles Caulier

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[Maik Qualmann]

https://bugs.kde.org/show_bug.cgi?id=391115

--- Comment #7 from Maik Qualmann  ---
Git commit 1f8edbda8bff2a3e201b8870a686f0066e20d590 by Maik Qualmann.
Committed on 22/04/2019 at 18:35.
Pushed by mqualmann into branch 'master'.

use QReadWriteLock for the CollectionManager
Loading a large item view is now about 4x
faster here with MySQL
Related: bug 398921, bug 397901, bug 391840

M  +12   -13   core/libs/database/collection/collectionmanager.cpp
M  +1-1core/libs/database/collection/collectionmanager.h
M  +11   -9core/libs/database/collection/collectionmanager_album.cpp
M  +118  -107  core/libs/database/collection/collectionmanager_location.cpp
M  +1-1core/libs/database/collection/collectionmanager_p.cpp
M  +2-0core/libs/database/collection/collectionmanager_p.h
M  +1-1core/libs/database/coredb/coredbaccess.cpp

https://invent.kde.org/kde/digikam/commit/1f8edbda8bff2a3e201b8870a686f0066e20d590

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[Maik Qualmann]

https://bugs.kde.org/show_bug.cgi?id=391115

--- Comment #6 from Maik Qualmann  ---
Git commit abd32eefb24ac94c6a5343869578eabdd9ec9f9d by Maik Qualmann.
Committed on 03/01/2019 at 19:43.
Pushed by mqualmann into branch 'master'.

use ReadWriteLock for the CollectionManager
Loading a large item view is now about 4x
faster here with MySQL
Related: bug 391840

M  +9-9core/libs/database/collection/collectionmanager_album.cpp
M  +43   -39   core/libs/database/collection/collectionmanager_location.cpp
M  +2-0core/libs/database/collection/collectionmanager_p.h

https://commits.kde.org/digikam/abd32eefb24ac94c6a5343869578eabdd9ec9f9d

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[MiK]

https://bugs.kde.org/show_bug.cgi?id=391115

Michal Kec (MiK)  changed:

   What|Removed |Added

 CC||k...@kecnet.cz

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[Michał Karwowski]

https://bugs.kde.org/show_bug.cgi?id=391115

--- Comment #5 from Michał Karwowski  ---
(In reply to Michał Karwowski from comment #4)
> Thanks for 'DebugView' - I didn't know such tool exists.
> 
> I made more detail research on the problem.
> 
> 
> To be precise, my total media count from DigiKam database stats is about
> 108012.
> (I didn't add all folders to DigiKam)
> 
> When I run the application I see in a log:
> [8144] digikam.database: Search result: 1512168
> 
> But: "1 512 168" is much more than "108 012"!
> Strange.. what does it mean?
> 
> 
> ==
> ==
> Text filter (available on the right) 
> ==
> ==
> 
> The problem with speed appears to be related to "Text filter" which I use.
> Filtering pictures with that filter takes from 20 seconds to 3 minutes.
> 
> DebugView doesn't show logs for database on "text filter".
> Thus now, I believe that's it's not related to database engine,
> but rather internal filtering system.
> 
> Filter query: 'tajlandia' took 2 minutes 31 seconds (to display results -
> 8586 items).
> Filter query: 'żaba' took 1 minute 25 seconds (to display results - 1862
> items).
> etc.
> Clearing filter also takes lots of time.
> 
> I did tests with offline and online albums.
> By term offline I mean disconnected USB drive with albums.
> By term online I mean connected USB drive with albums.
> Measurements I have don after the inilial digikam scan.
> 
> 
> ==
> ==
> Search (available on the left) 
> ==
> ==
> When I use search I see digikam.database logs on DebugView.
> This suggests that for searching database query is made in contrast to
> filtering.
> 
> To be clear I use: digiKam  5.8.0 with mariadb 10.2.10 winx64
> 
> 
> Query for: "gosia" (164794 results?)
> 
> 1448  21:55:15[10424] digikam.general: keywords changed to ' 
> "gosia" '
> 1449  21:55:15[10424] digikam.geoiface:   
> 1450  21:55:15[10424] digikam.geoiface:   
> 1451  21:55:15[10424] digikam.geoiface:   
> 1452  21:55:15[10424] digikam.general: Using  8  CPU core to 
> run threads  
> 1453  21:55:15[10424] digikam.general: Action Thread run  1  
> new jobs 
> 1454  21:55:15[10424] digikam.database: " ( (  
> (Albums.relativePath LIKE
> ?) OR (Images.name LIKE ?) OR (Images.id IN(SELECT imageid FROM
> ImageTags WHERE tagid IN(SELECT id FROM Tags WHERE name LIKE ?))) OR
> (Albums.caption LIKE ?) OR (Albums.collection LIKE ?) OR (Images.id IN 
> (SELECT imageid FROM ImageComments   WHERE type=? AND comment LIKE ?)) OR
> (Images.id IN  (SELECT imageid FROM ImageComments   WHERE type=? AND comment
> LIKE ?))  ) ) "   
> 1455  21:55:15[10424] digikam.database: Search query: 
> 1458  21:55:16[10424] digikam.database: Search result: 164794 
> # Result is ready but not visible
> 
> 1459  21:55:19[10424] digikam.general: Cancel Main Thread 
> 1507  21:55:22[10424] digikam.general: One job is done
> 1508  21:55:24[10424] digikam.general: Cancel Main Thread 
> 1509  21:55:45[10424] digikam.geoiface:   
> 1512  21:55:50[10424] digikam.general: keywords changed to ' 
> "gosia" '
> 1513  21:55:50[10424] digikam.general: same keywords as 
> before,
> ignoring...   
> 1514  21:55:58[10424] digikam.general: keywords changed to ' 
> "ekp" '  
> # application is responsive again
> 
> 
> 
> 
> Query for: "ekp" (394240 results?)
> 
> 
> 1514  21:55:58[10424] digikam.general: keywords changed to ' 
> "ekp" '  
> 1515  21:55:58[10424] digikam.geoiface:   
> 1516  21:55:58[10424] digikam.geoiface:   
> 1517  21:55:58[10424] digikam.geoiface:   
> 1518  21:55:58[10424] digikam.general: Using  8  CPU core to 
> run threads  
> 1519  21:55:58[10424] digikam.general: Action Thread run  1  
> new jobs 
> 1520  21:55:58[10424] digikam.database: " ( (  
> (Albums.relativePath LIKE
> ?) OR (Images.name LIKE ?) OR (Images.id IN(SELECT imageid FROM
> ImageTags WHERE tagid IN(SELECT id FROM Tags WHERE name LIKE ?))) OR
> (Albums.caption LIKE ?) OR (Albums.collection LIKE ?) OR (Images.id IN 
> (SELECT imageid FROM ImageComments   WHERE type=? AND comment LIKE ?)) OR
> (Images.id IN  (SELECT imageid FROM ImageComments   WHERE type=? AND comment
> LIKE ?))  ) ) "   
> 1521  21:55:58[10424] digikam.database: Search query: 
> 1524  21:56:00[10424] 

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[Michał Karwowski]

https://bugs.kde.org/show_bug.cgi?id=391115

--- Comment #4 from Michał Karwowski  ---
Thanks for 'DebugView' - I didn't know such tool exists.

I made more detail research on the problem.


To be precise, my total media count from DigiKam database stats is about
108012.
(I didn't add all folders to DigiKam)

When I run the application I see in a log:
[8144] digikam.database: Search result: 1512168

But: "1 512 168" is much more than "108 012"!
Strange.. what does it mean?


==
==
Text filter (available on the right) 
==
==

The problem with speed appears to be related to "Text filter" which I use.
Filtering pictures with that filter takes from 20 seconds to 3 minutes.

DebugView doesn't show logs for database on "text filter".
Thus now, I believe that's it's not related to database engine,
but rather internal filtering system.

Filter query: 'tajlandia' took 2 minutes 31 seconds (to display results - 8586
items).
Filter query: 'żaba' took 1 minute 25 seconds (to display results - 1862
items).
etc.
Clearing filter also takes lots of time.

I did tests with offline and online albums.
By term offline I mean disconnected USB drive with albums.
By term online I mean connected USB drive with albums.
Measurements I have don after the inilial digikam scan.


==
==
Search (available on the left) 
==
==
When I use search I see digikam.database logs on DebugView.
This suggests that for searching database query is made in contrast to
filtering.

To be clear I use: digiKam  5.8.0 with mariadb 10.2.10 winx64


Query for: "gosia" (164794 results?)

144821:55:15[10424] digikam.general: keywords changed to '
"gosia" '
144921:55:15[10424] digikam.geoiface:   
145021:55:15[10424] digikam.geoiface:   
145121:55:15[10424] digikam.geoiface:   
145221:55:15[10424] digikam.general: Using  8  CPU core to
run threads  
145321:55:15[10424] digikam.general: Action Thread run  1 
new jobs 
145421:55:15[10424] digikam.database: " ( ( 
(Albums.relativePath LIKE ?) OR (Images.name LIKE ?) OR (Images.id IN   
(SELECT imageid FROM ImageTags WHERE tagid IN(SELECT id FROM Tags WHERE
name LIKE ?))) OR (Albums.caption LIKE ?) OR (Albums.collection LIKE ?) OR
(Images.id IN  (SELECT imageid FROM ImageComments   WHERE type=? AND comment
LIKE ?)) OR (Images.id IN  (SELECT imageid FROM ImageComments   WHERE type=?
AND comment LIKE ?))  ) ) "  
145521:55:15[10424] digikam.database: Search query: 
145821:55:16[10424] digikam.database: Search result: 164794 
# Result is ready but not visible

145921:55:19[10424] digikam.general: Cancel Main Thread 
150721:55:22[10424] digikam.general: One job is done
150821:55:24[10424] digikam.general: Cancel Main Thread 
150921:55:45[10424] digikam.geoiface:   
151221:55:50[10424] digikam.general: keywords changed to '
"gosia" '
151321:55:50[10424] digikam.general: same keywords as
before, ignoring...   
151421:55:58[10424] digikam.general: keywords changed to '
"ekp" '  
# application is responsive again




Query for: "ekp" (394240 results?)


151421:55:58[10424] digikam.general: keywords changed to '
"ekp" '  
151521:55:58[10424] digikam.geoiface:   
151621:55:58[10424] digikam.geoiface:   
151721:55:58[10424] digikam.geoiface:   
151821:55:58[10424] digikam.general: Using  8  CPU core to
run threads  
151921:55:58[10424] digikam.general: Action Thread run  1 
new jobs 
152021:55:58[10424] digikam.database: " ( ( 
(Albums.relativePath LIKE ?) OR (Images.name LIKE ?) OR (Images.id IN   
(SELECT imageid FROM ImageTags WHERE tagid IN(SELECT id FROM Tags WHERE
name LIKE ?))) OR (Albums.caption LIKE ?) OR (Albums.collection LIKE ?) OR
(Images.id IN  (SELECT imageid FROM ImageComments   WHERE type=? AND comment
LIKE ?)) OR (Images.id IN  (SELECT imageid FROM ImageComments   WHERE type=?
AND comment LIKE ?))  ) ) "  
152121:55:58[10424] digikam.database: Search query: 
152421:56:00[10424] digikam.database: Search result: 394240 
152521:56:08[10424] digikam.general: Cancel Main Thread 
152621:56:18[10424] digikam.general: One job is done
157521:56:23[10424] 

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[Maik Qualmann]

https://bugs.kde.org/show_bug.cgi?id=391115

Maik Qualmann  changed:

   What|Removed |Added

 CC||metzping...@gmail.com

--- Comment #3 from Maik Qualmann  ---
I see a big difference in the syntax between SQLite and MySQL when using MATCH.
MYSQL requires a FULLTEXT index for the table columns, and SQLite requires FTS5
tables. If I see it correctly, MATCH does not really find parts within a word,
although there is the * wildcard, but the search is only from the beginning of
the word. I can not imagine that the search takes minutes. I have about 50k
images here, a search takes about 1 second. The problem is more because when a
lot of images have been found, it takes a little while until the icon model is
loaded. Please install DebugView from Microsoft. How long does it take to enter
the search text until you see "digikam.database: Search result: " in the
log?

Maik

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=391115

caulier.gil...@gmail.com changed:

   What|Removed |Added

Version|unspecified |5.8.0

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[Michał Karwowski]

https://bugs.kde.org/show_bug.cgi?id=391115

--- Comment #2 from Michał Karwowski  ---
2018-02-26 19:20 GMT+01:00 :

> https://bugs.kde.org/show_bug.cgi?id=391115
>
> caulier.gil...@gmail.com changed:
>
>What|Removed |Added
> 
> 
>  CC||caulier.gil...@gmail.com
>
> --- Comment #1 from caulier.gil...@gmail.com ---
> Thanks for this analysis and for the proposal to improve SQL queries.
>
> Just to be sure, which digiKam version do you use exactly ?
>

*I'm using DigiKam 5.8.0 (on Windows 7 x64, currently with MySQL - mariadb
10.2.10 winx64).*

DigiKam seems to be best choice in the future (if search speed would be
improved).
I have tried many applications in different configurations but most of them
have some issues.
Some programs doesn't allow to search for file/folder names or time range
(limitting to time range from NOW only), sometimes accidently disconecting
USB drive couses huge library to disapear for ever).
DigiKam doesn't has such problems. In DigiKam we had speed issues which
disqualifies this application for larger databases.

It seems to be realy good application. I spend many hours to find out, why
searches in digikam are so slow. It is written in C++, and Qt -
technologies which I completly dont undertand :(
But I home introducing full text search (instead of 'like' queries) would
be quite easy for people having that technologies.


best regards,
Michał Karwowski


>
> Gilles Caulier
>
> --
> You are receiving this mail because:
> You reported the bug.

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 391115] Very slow search, full text search (FTS) proposition for huge speedup.

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=391115

caulier.gil...@gmail.com changed:

   What|Removed |Added

 CC||caulier.gil...@gmail.com

--- Comment #1 from caulier.gil...@gmail.com ---
Thanks for this analysis and for the proposal to improve SQL queries.

Just to be sure, which digiKam version do you use exactly ?

Gilles Caulier

-- 
You are receiving this mail because:
You are watching all bug changes.