Bug 341111 - MYSQL : deleting an image involves "too many" queries
Summary: MYSQL : deleting an image involves "too many" queries
Status: RESOLVED FIXED
Alias: None
Product: digikam
Classification: Unclassified
Component: Database-Mysql (show other bugs)
Version: 4.4.0
Platform: openSUSE RPMs Linux
: NOR normal (vote)
Target Milestone: ---
Assignee: Digikam Developers
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-11-20 02:07 UTC by Roy Bellingan
Modified: 2019-11-07 22:08 UTC (History)
3 users (show)

See Also:
Latest Commit:
Version Fixed In: 7.0.0


Attachments
Queries in trace (102.43 KB, text/plain)
2016-07-17 14:06 UTC, swatilodha27
Details

Note You need to log in before you can comment on or make changes to this bug.
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