[Digikam-devel] extragear/graphics/digikam/libs/database

Marcel Wiesweg marcel.wiesweg at gmx.de
Sun Jun 14 19:09:20 BST 2009


>
> 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

                                                                                          



More information about the Digikam-devel mailing list