extragear/graphics/digikam/libs/database

SVN commit 981989 by aclemens:

Optimize query

The keywords IN, NOT IN, !, <>, OR, NOT EXISTS will not use the index of
the table and therefore perform bad.
The new query will also reduce the SQL statements from 46 to 24
instructions, which is good ;-)

Unfortunately the query is still very slow, because the index is set on
path for FilePaths and uniqueHash for Thumbnails.
This is why a lot of thumb queries with the new thumbsDB seem to be
slower. When joining the FilePaths table with the Thumbnails table, we
are not using both indexes, but only one, therefore we don't benefit
from the index tables.

My thumbsDB is 600MB and is really slow to query. We either need to
think of new index tables or try to improve speed with other solutions.
When thumbsDB grows even more, digiKam becomes unusable on my system.

Andi

CCMAIL:[hidden email]

 M  +3 -3      thumbnaildb.cpp  


--- trunk/extragear/graphics/digikam/libs/database/thumbnaildb.cpp #981988:981989
@@ -134,9 +134,9 @@
     query = d->db->prepareQuery(QString("SELECT path, id "
                                         "FROM FilePaths "
                                         "   INNER JOIN Thumbnails ON FilePaths.thumbId=Thumbnails.id "
-                                        "WHERE Thumbnails.type NOT IN(%1,%2);")
-                                .arg(DatabaseThumbnail::UndefinedType)
-                                .arg(DatabaseThumbnail::NoThumbnail));
+                                        "WHERE type BETWEEN %1 AND %2;")
+                                .arg(DatabaseThumbnail::PGF)
+                                .arg(DatabaseThumbnail::JPEG2000));
 
     if (!d->db->exec(query))
         return QHash<QString, int>();
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Marcel Wiesweg]

>
> Unfortunately the query is still very slow, because the index is set on
> path for FilePaths and uniqueHash for Thumbnails.
> This is why a lot of thumb queries with the new thumbsDB seem to be
> slower. When joining the FilePaths table with the Thumbnails table, we
> are not using both indexes, but only one, therefore we don't benefit
> from the index tables.

There is an extra index on thumbId for both tables. The UNIQUE creates an index as well,
so we have an index to look up by path and one on thumbId.
There are no more entries in the table than the two ;-)

The statement most important for digikam performance is
ThumbnailDB::findByHash.

SELECT id, type, modificationDate, orientationHint, data
FROM UniqueHashes   INNER JOIN Thumbnails
ON thumbId = id WHERE uniqueHash=?  AND fileSize=?;

In my understanding, this will use the index created by the UNIQUE(uniqueHash, fileSize) statement.
This is the trace, which I dont know to interpret:

addr  opcode         p1    p2    p3    p4             p5  comment                                                                                
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0     EXPLAIN SELECT id, type, modificationDate, orientationHint, data FROM UniqueHashes   INNER JOIN Thumbnails ON thumbId = id WHERE uniqueHash='' AND fileSize=0;  00
1     String8        0     1     0                    00
2     Integer        0     2     0                    00
3     Goto           0     33    0                    00
4     SetNumColumns  0     3     0                    00
5     OpenRead       0     3     0                    00
6     SetNumColumns  0     3     0                    00
7     OpenRead       2     4     0     keyinfo(2,BINARY,BINARY)  00
8     SetNumColumns  0     5     0                    00
9     OpenRead       1     2     0                    00
10    SCopy          1     4     0                    00
11    IsNull         4     29    0                    00
12    SCopy          2     5     0                    00
13    IsNull         5     29    0                    00
14    Affinity       4     2     0     adb            00
15    MoveGe         2     29    4     2              00
16    IdxGE          2     29    4     2              01
17    IdxRowid       2     9     0                    00
18    MoveGe         0     0     9                    00
19    Column         0     2     9                    00
20    MustBeInt      9     28    0                    00
21    NotExists      1     28    9                    00
22    Rowid          1     10    0                    00
23    Column         1     1     11                   00
24    Column         1     2     12                   00
25    Column         1     3     13                   00
26    Column         1     4     14                   00
27    ResultRow      10    5     0                    00
28    Next           2     16    0                    00
29    Close          0     0     0                    00
30    Close          2     0     0                    00
31    Close          1     0     0                    00
32    Halt           0     0     0                    00
33    Transaction    0     0     0                    00
34    VerifyCookie   0     7     0                    00
35    TableLock      0     3     0     UniqueHashes   00
36    TableLock      0     2     0     Thumbnails     00
37    Goto           0     4     0                    00

                                                                                         
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel