Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Index: Improve the query performance when flagging hidden files #2928

Closed
jerome-cui opened this issue Nov 21, 2022 · 6 comments
Closed

Index: Improve the query performance when flagging hidden files #2928

jerome-cui opened this issue Nov 21, 2022 · 6 comments
Assignees
Labels
enhancement Refactoring, improvement or maintenance task released Available in the stable release

Comments

@jerome-cui
Copy link

jerome-cui commented Nov 21, 2022

I noticed that there is a SQL query causes the CPU to 100% usage all the time, below is the information I found in db slow log:

# Time: 221120 17:13:36
# User@Host: photoprism[photoprism] @  [172.24.0.2]
# Thread_id: 392  Schema: photoprism  QC_hit: No
# Query_time: 6288.676700  Lock_time: 0.000035  Rows_sent: 0  Rows_examined: 1836237798
# Rows_affected: 0  Bytes_sent: 0
SET timestamp=1668935616;
UPDATE `photos` SET `photo_quality` = -1  WHERE (id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND file_missing = 0 AND file_error = '' AND deleted_at IS NULL));

This maybe the issue of Mariadb because it works with this query "select count(*) from photos WHERE (id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND file_missing = 0 AND file_error = '' AND deleted_at IS NULL))", but how could I avoid this issue in Photoprism?

Database: Mariadb 10.9.3.
Row count of table 'photos': 86789.

@jerome-cui jerome-cui added the bug Something isn't working label Nov 21, 2022
@lastzero
Copy link
Member

You can suggest a different query that does the same.

@lastzero lastzero added enhancement Refactoring, improvement or maintenance task help wanted Well suited for external contributors! and removed bug Something isn't working labels Nov 21, 2022
@lastzero lastzero changed the title DB performance issue Index: Improve the query performance when flagging hidden files Nov 21, 2022
@jerome-cui
Copy link
Author

It happens after I migrated the database from DB in docker to DB in host. It has no problem for db in docker so I tried to re-import the database again and seems it works now.
Sorry for this report and I will close it.

lastzero added a commit that referenced this issue Nov 21, 2022
Signed-off-by: Michael Mayer <michael@photoprism.app>
@lastzero
Copy link
Member

I have already changed the queries. It would be great if you could test it when a new development preview is available later today!

@lastzero lastzero reopened this Nov 21, 2022
@lastzero lastzero added please-test Ready for acceptance test and removed help wanted Well suited for external contributors! labels Nov 21, 2022
@lastzero lastzero self-assigned this Nov 21, 2022
@lastzero
Copy link
Member

Any update on this?

@jerome-cui
Copy link
Author

Any update on this?

Sorry for the late reply. I did some test on the latest preview and there is no more slow sql query like this.
Thanks for your work.

@lastzero
Copy link
Member

Excellent, thank you!

@lastzero lastzero added tested Changes have been tested successfully and removed please-test Ready for acceptance test labels Nov 25, 2022
@graciousgrey graciousgrey added released Available in the stable release and removed tested Changes have been tested successfully labels May 3, 2023
@lastzero lastzero moved this to Released 🌈 in Roadmap 🚀✨ Jun 8, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement Refactoring, improvement or maintenance task released Available in the stable release
Projects
Status: Release 🌈
Development

No branches or pull requests

3 participants