Check and Optimize digiKam’s Databases

By default, digiKam uses two SQLite databases for storing essential data: digikam4.db and thumbnails-digikam.db. And to make the application run fast and smoothly, it’s a good idea to check and optimize the databases every now and then. To do this on Ubuntu or its derivatives, you need to install the sqlite3 package using the sudo apt-get install sqlite3 command. Once you’ve done that, back up the digikam4.db and thumbnails-digikam.db databases. Open then the terminal and switch to the directory where the databases are stored. First off, you should check the integrity of both databases using the following commands:

sqlite3 -line digikam4.db 'pragma integrity_check;'
sqlite3 -line thumbnails-digikam.db 'pragma integrity_check;'

If both databases are in order, you should see the integrity_check = ok message. To optimize the databases, run the two commands below:

sqlite3 -line digikam4.db 'vacuum;'
sqlite3 -line thumbnails-digikam.db 'vacuum;'

Run then the integrity check once again to make sure that everything works properly, and you are done.

Freelance tech writer covering Linux and open source software.

Tagged with: , ,
Posted in Open Source, Software
13 comments on “Check and Optimize digiKam’s Databases
  1. shaforostoff says:

    why not do this automatically with KIdleTime?

  2. anonymous says:

    Yes, most users have not even an idea what a database is, let alone that they know how to optimize it.

    Still, they will expect digikam to keep working properly.

  3. jstaniek says:

    There are also extra optimization options: http://community.kde.org/Kexi/Links#Optimizing :)

  4. BroX says:

    Thanks for the tip.

    For those who know what a database is, but not familiar with sqlite commands, it would be helpful to explain what the above commands actually do.

  5. Ahmed says:

    As Far As I Am Concerned digiKam doesn’t have much performance, i think the developers should spend a release which is dedicated to nothing but optimising performance to the entire application. Like

    Decrease Start up time!!! With Scan For New Images Enabled My System Takes About 90seconds to load!!

    Decrease the time taken to rebuild thumbnails, in my 75000 pictures collection it takes 5-6 hrs to rebuild all thumbails!!

    Decrease the time taken to rebuild finger prints, in my case it takes abt 6-7 hrs to rebuild it?

    Make face detection and recognition really work!!

    I do complete agree that digiKam is class apart compared to other applications when it comes to features and what it can do? However there are some users who want performance? So Why Not Increase The Performance!

  6. Fri13 says:

    With a database itself:

    $ sqlite3 -line digikam4.db ‘pragma integrity_check;’
    integrity_check = *** in database main ***
    Fragmentation of 24 bytes reported as 0 on page 57156
    Fragmentation of 0 bytes reported as 1 on page 57155
    On tree page 56789 cell 59: Rowid 9 out of order (previous was 56703)
    On tree page 57154 cell 4: Rowid 9 out of order (previous was 56709)
    On tree page 57154 cell 4: invalid page number 908931379

    integrity_check = rowid 329095 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329106 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329107 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329108 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329109 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329110 missing from index copyright_imageid_index

    integrity_check = rowid 329110 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329111 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329112 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329113 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329114 missing from index copyright_imageid_index

    integrity_check = rowid 329114 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329115 missing from index copyright_imageid_index

    integrity_check = rowid 329115 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329116 missing from index copyright_imageid_index

    integrity_check = rowid 329116 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329117 missing from index copyright_imageid_index

    integrity_check = rowid 329117 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329118 missing from index copyright_imageid_index

    integrity_check = rowid 329118 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329119 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329120 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329122 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329124 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329126 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329127 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329128 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329130 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329131 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329132 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329133 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329134 missing from index copyright_imageid_index

    integrity_check = rowid 329135 missing from index copyright_imageid_index

    integrity_check = rowid 329135 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329136 missing from index copyright_imageid_index

    integrity_check = rowid 329137 missing from index copyright_imageid_index

    integrity_check = rowid 329137 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329138 missing from index copyright_imageid_index

    integrity_check = rowid 329138 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329139 missing from index copyright_imageid_index

    integrity_check = rowid 329139 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329140 missing from index copyright_imageid_index

    integrity_check = rowid 329140 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329141 missing from index copyright_imageid_index

    integrity_check = rowid 329141 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329142 missing from index copyright_imageid_index

    integrity_check = rowid 329142 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329143 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329144 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329145 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329146 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329147 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329148 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329149 missing from index copyright_imageid_index

    integrity_check = rowid 329150 missing from index copyright_imageid_index

    integrity_check = rowid 329150 missing from index sqlite_autoindex_ImageCopyright_1

    integrity_check = rowid 329152 missing from index copyright_imageid_index

    integrity_check = rowid 329153 missing from index copyright_imageid_index

    integrity_check = rowid 329154 missing from index copyright_imageid_index

    integrity_check = rowid 329155 missing from index copyright_imageid_index

    integrity_check = rowid 329156 missing from index copyright_imageid_index

    integrity_check = rowid 329157 missing from index copyright_imageid_index

    integrity_check = rowid 329158 missing from index copyright_imageid_index

    integrity_check = rowid 329162 missing from index copyright_imageid_index

    integrity_check = rowid 329163 missing from index copyright_imageid_index

    integrity_check = rowid 329165 missing from index copyright_imageid_index

    integrity_check = rowid 329168 missing from index copyright_imageid_index

    integrity_check = rowid 329170 missing from index copyright_imageid_index

    integrity_check = rowid 329174 missing from index copyright_imageid_index

    integrity_check = rowid 329176 missing from index copyright_imageid_index

    integrity_check = rowid 329178 missing from index copyright_imageid_index

    integrity_check = rowid 56704 missing from index hash_index
    Error: database disk image is malformed

    ——————————————————–

    And when done with vacuum:

    $ sqlite3 -line digikam4.db ‘vacuum;’
    Error: PRIMARY KEY must be unique

    This kind tools to repair database should be added to digiKam settings > database.

  7. Noname says:

    I agree that Digikam should spend some time on optimizing its performance. It’s a very good application, but performance is severely lacking. E.g. there is still no real multithreading, as most operations only use one core. This and other performance bottelnecks make Digikam a pain to use when dealing with current multi-pixel photos.

  8. Andi Clemens says:

    For the vacuum-part, there is already a tool in the digiKam package. I added it as a bashscript, it can be run with “cleanup_digikamdb”. See “cleanup_digikamdb -h” for other options.

  9. kameratkamera says:

    Thanks for the tip!

    I love digikam, but had some problems with it, so i stopped using it.

    Testing these commands, the first one gives ‘ok’, but the second one doesn’t give a result at all. I just get a > and the cursor.

    What’s wrong, and how can i fix it?

  10. Axel says:

    Hi Dimitri:

    This is a good and helpful posting. You talk about check and optimize.

    ?? But what to do, if your hints evaluate a damaged database? As an engaged users, I feel quite alone. What to do, how to react?

    Hints for database repair?

    Its great to have other tools, Andi. For my opinion, it feels like fast running without taking off!

    I was/am in contact with http://linuxdarkroom.tassy.net/digikam-db-validator/ , as mentioned above.

    Attempt to check in background automatically would be better, I think?

    Mozilla uses a number of copies (I believe 7 over one week), so users do not even notice a big gap in usage.

    Axel

  11. aaa@aaa.com says:

    Offer an option in the menus to do it, or do it automagically in the background or after every X startups.

    Command line is not an option for software like this one. Also this tip is not multiplatform.

  12. Willem van den Kerkhoff says:

    All very nice, but can you please explain to me why after going to the painful process of installing Digikam on my Macbook Pro I can’t find any databases of the names you specified? Are they not automatically created, and if not how can I do that?

Comments are closed.

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Follow

Get every new post delivered to your Inbox.

Join 460 other followers

%d bloggers like this: