Bug 341111

Summary:	MYSQL : deleting an image involves "too many" queries
Product:	[Applications] digikam	Reporter:	Roy Bellingan <admin>
Component:	Database-Mysql	Assignee:	Digikam Developers <digikam-bugs-null>
Status:	RESOLVED FIXED
Severity:	normal	CC:	caulier.gilles, metzpinguin, swatilodha27
Priority:	NOR
Version:	4.4.0
Target Milestone:	---
Platform:	openSUSE
OS:	Linux
Latest Commit:		Version Fixed In:	7.0.0
Sentry Crash Report:
Attachments:	Queries in trace

Description Roy Bellingan 2014-11-20 02:07:18 UTC

Deleting an image from an album with 1K photos requires around 2K queries.
Essentially it reloads the whole album metadata.

Reproducible: Always

Steps to Reproduce:
1.Open an album and start to browser
2.In mysql start to log the query 
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';
USE mysql;
TRUNCATE TABLE `general_log`;
3.Delete a photo
4.SET GLOBAL general_log = 'OFF';
select * from general_log;

Actual Results:  
The whole album metadata is reloaded

Expected Results:  
Just the image I'm going to see is refreshed.

Comment 1 caulier.gilles 2016-07-06 20:35:03 UTC

This file still valid using last digiKam 5.0.0 ?

Gilles Caulier

Comment 2 caulier.gilles 2016-07-16 13:20:55 UTC

Swati,

Do you confirm this file ?

Gilles Caulier

Comment 3 swatilodha27 2016-07-17 09:04:30 UTC

I don't actually find a bug in this condition. I deleted an image and on seeing the general query log, it included the SQL queries to update the rest of images in that album. I think this is how it should work.

Could you elaborate "Expected Results:  Just the image I'm going to see is refreshed."? Since, you've deleted the image, which image are you exactly talking about?

Thanks.

Comment 4 caulier.gilles 2016-07-17 13:57:34 UTC

Swati,

Are you tried to follow all steps to reproduce described in original post to trace all queries done while deletion ? How many queries do you see in trace in this condition ?

Gilles Caulier

Comment 5 swatilodha27 2016-07-17 14:06:28 UTC

Created attachment 100138 [details]
Queries in trace

Comment 6 swatilodha27 2016-07-17 14:07:04 UTC

Yes, I did follow all the steps.

Please find the attachment that includes the queries.

Comment 7 caulier.gilles 2016-07-17 14:12:36 UTC

Conclusion ?
How many queries send to the server for an item removed from an album with 1K items ?

Gilles

Comment 8 swatilodha27 2016-07-17 15:01:22 UTC

Yeah, I got it. You're right about that. 

So what would be the "expected result" in such case, involving thousands  of images in an album ?

Comment 9 Roy Bellingan 2016-07-17 16:23:13 UTC

Hy to all!

I think that the expected result is "no query" at all, we already have all the info of the folder, so why reload ?

At the time of the bug report my programming knowledge was "scarce", but now I think I can even try to fix this! 

I download now the code and start looking around, do anyone has suggestion on where to start ?

Comment 10 caulier.gilles 2016-07-17 17:01:16 UTC

Swati, Roy,

In theory, 2K of request for 1K of items is logic. To display icon view, for each item, from the database we need :

1/ the image information as comments, tags, rating, labels, GPS, etc.
2/ the thumbnail.

But, we have the cache between icon view and database.

So, i suspect 2 points :

1/ for obscur reason the cache was broken when the report have been written,
2/ the cache was not enough large for 1K items.

I just tested, and i'm sure that at least, cache for thumbnails work fine (100 items here).

Gilles Caulier

Comment 11 caulier.gilles 2016-11-25 20:12:54 UTC

What's about this file using digiKam AppImage bundle 5.4.0 pre release given at
this url :

https://drive.google.com/drive/folders/0BzeiVr-byqt5Y0tIRWVWelRJenM

Gilles Caulier

Comment 12 Maik Qualmann 2019-11-07 22:08:06 UTC

This bug was fixed by Bug 375317, so I close this bug now.

Maik