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

[Andi Clemens]

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:digikam-devel at kde.org

 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>();