[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