starred/photoprism

Fork 0

mirror of https://github.com/photoprism/photoprism.git synced 2026-03-02 22:57:18 -05:00

Photos don't load #98

New issue

Closed

opened 2026-02-19 23:02:17 -05:00 by deekerman · 9 comments

deekerman commented

2026-02-19 23:02:17 -05:00

Owner

Originally created by @p80n on GitHub (Jun 27, 2019).

Originally assigned to: @lastzero on GitHub.

Tried to update to a more recent docker image, but if I use an image more recent than 20190607, the initial gallery page has the nav bar and a progress bar, but photos never load.

I wonder if it could be related to the addition of SQL_CALC_FOUND_ROWS to this query:
github.com/photoprism/photoprism@5f63f2999c/internal/photoprism/search.go (L51)

While the page is attempting to load, I see this query repeated three times in MySQL's process list:

SELECT SQL_CALC_FOUND_ROWS photos.*, files.id AS file_id, files.file_primary, files.file_missing, files.file_name, files.file_hash, files.file_type, files.file_mime, files.file_width, files.file_height, files.file_aspect_ratio, files.file_orientation, files.file_main_color, files.file_colors, files.file_luminance, files.file_chroma, cameras.camera_make, cameras.camera_model, lenses.lens_make, lenses.lens_model, countries.country_name, locations.loc_display_name, locations.loc_name, locations.loc_city, locations.loc_postcode, locations.loc_county, locations.loc_state, locations.loc_country, locations.loc_country_code, locations.loc_category, locations.loc_type, GROUP_CONCAT(labels.label_name) AS labels FROM `photos` JOIN files ON files.photo_id = photos.id AND files.file_primary AND files.deleted_at IS NULL JOIN cameras ON cameras.id = photos.camera_id JOIN lenses ON lenses.id = photos.lens_id LEFT JOIN countries ON countries.id = photos.country_id LEFT JOIN locations ON locations.id = photos.location_id LEFT JOIN photos_labels ON photos_labels.photo_id = photos.id LEFT JOIN labels ON photos_labels.label_id = labels.id WHERE (photos.deleted_at IS NULL AND files.file_missing = 0) GROUP BY photos.id, files.id ORDER BY taken_at DESC LIMIT 60 OFFSET 0

For my install, with ~6,800 photos, this query is taking about 5 minutes to execute.

On a side note, a bit of googling seems to indicate SQL_CALC_FOUND_ROWS may not be optimal. Apparently it's been deprecated in MySQL.
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html

Let me know if I can provide other info. Thanks!

Originally created by @p80n on GitHub (Jun 27, 2019). Originally assigned to: @lastzero on GitHub. Tried to update to a more recent docker image, but if I use an image more recent than `20190607`, the initial gallery page has the nav bar and a progress bar, but photos never load. I wonder if it could be related to the addition of `SQL_CALC_FOUND_ROWS` to this query: https://github.com/photoprism/photoprism/blob/5f63f2999c7111a7ff60154689aec67abcb577e4/internal/photoprism/search.go#L51 While the page is attempting to load, I see this query repeated three times in MySQL's process list: ``` SELECT SQL_CALC_FOUND_ROWS photos.*, files.id AS file_id, files.file_primary, files.file_missing, files.file_name, files.file_hash, files.file_type, files.file_mime, files.file_width, files.file_height, files.file_aspect_ratio, files.file_orientation, files.file_main_color, files.file_colors, files.file_luminance, files.file_chroma, cameras.camera_make, cameras.camera_model, lenses.lens_make, lenses.lens_model, countries.country_name, locations.loc_display_name, locations.loc_name, locations.loc_city, locations.loc_postcode, locations.loc_county, locations.loc_state, locations.loc_country, locations.loc_country_code, locations.loc_category, locations.loc_type, GROUP_CONCAT(labels.label_name) AS labels FROM `photos` JOIN files ON files.photo_id = photos.id AND files.file_primary AND files.deleted_at IS NULL JOIN cameras ON cameras.id = photos.camera_id JOIN lenses ON lenses.id = photos.lens_id LEFT JOIN countries ON countries.id = photos.country_id LEFT JOIN locations ON locations.id = photos.location_id LEFT JOIN photos_labels ON photos_labels.photo_id = photos.id LEFT JOIN labels ON photos_labels.label_id = labels.id WHERE (photos.deleted_at IS NULL AND files.file_missing = 0) GROUP BY photos.id, files.id ORDER BY taken_at DESC LIMIT 60 OFFSET 0 ``` For my install, with ~6,800 photos, this query is taking about 5 minutes to execute. On a side note, a bit of googling seems to indicate `SQL_CALC_FOUND_ROWS` may not be optimal. Apparently it's been deprecated in MySQL. https://dev.mysql.com/doc/refman/8.0/en/information-functions.html Let me know if I can provide other info. Thanks!

deekerman

2026-02-19 23:02:17 -05:00

closed this issue
added the
released

bug
labels

deekerman commented

2026-02-19 23:02:18 -05:00

Author

Owner

@p80n commented on GitHub (Jun 27, 2019):

Actually, just manually ran that query without SQL_CALC_FOUND_ROWS, still takes a bit on my host. So maybe it's more related to the joins involved.
hth

@p80n commented on GitHub (Jun 27, 2019): Actually, just manually ran that query without `SQL_CALC_FOUND_ROWS`, still takes a bit on my host. So maybe it's more related to the joins involved. hth

deekerman commented

2026-02-19 23:02:18 -05:00

Author

Owner

@lastzero commented on GitHub (Jun 27, 2019):

You are 100% right: The query is not optimized and we can remove SQL_CALC_FOUND_ROWS as we don't display the total number of photos in our app at the moment. We tried to get this to work with GORM just in case as running a second query is not always reliable and may also be slower.

Is the search in the app also slow or just if you run the query manually? Any other issues you noticed?

Note that this is a preview and database tables are still changing a lot - you should be prepared to re-build your index after an upgrade. This may also lead to performance improvements (maybe not in this case).

@lastzero commented on GitHub (Jun 27, 2019): You are 100% right: The query is not optimized and we can remove SQL_CALC_FOUND_ROWS as we don't display the total number of photos in our app at the moment. We tried to get this to work with GORM just in case as running a second query is not always reliable and may also be slower. Is the search in the app also slow or just if you run the query manually? Any other issues you noticed? Note that this is a preview and database tables are still changing a lot - you should be prepared to re-build your index after an upgrade. This may also lead to performance improvements (maybe not in this case).

deekerman commented

2026-02-19 23:02:18 -05:00

Author

Owner

@lastzero commented on GitHub (Jun 27, 2019):

Also using MySQL instead of the built-in TiDB might be faster (or slower). Would be amazing if you can test the difference for us! 👍

@lastzero commented on GitHub (Jun 27, 2019): Also using MySQL instead of the built-in TiDB might be faster (or slower). Would be amazing if you can test the difference for us! 👍

deekerman commented

2026-02-19 23:02:18 -05:00

Author

Owner

@p80n commented on GitHub (Jun 28, 2019):

It looks like switching to TiDB fixes the issue. Same query runs in ~1.5 seconds!
I'm going to update MySQL and do a bit more testing (just cause I'm kind of shocked it's that large of a difference). I'll post an update if I run across anything interesting.

@p80n commented on GitHub (Jun 28, 2019): It looks like switching to TiDB fixes the issue. Same query runs in ~1.5 seconds! I'm going to update MySQL and do a bit more testing (just cause I'm kind of shocked it's that large of a difference). I'll post an update if I run across anything interesting.

deekerman commented

2026-02-19 23:02:19 -05:00

Author

Owner

@lastzero commented on GitHub (Jul 3, 2019):

Removed SQL_CALC_FOUND_ROWS and added an index to photo_lat, photo_long and taken_at. Let us know if that helps. If yes, please close the ticket 👍

Also updated the docs at https://docs.photoprism.org/en/latest/setup/ - we changed the port and path in the Docker image, so this was outdated. How did you run / install PhotoPrism? Any issues?

@lastzero commented on GitHub (Jul 3, 2019): Removed `SQL_CALC_FOUND_ROWS` and added an index to `photo_lat`, `photo_long` and `taken_at`. Let us know if that helps. If yes, please close the ticket 👍 Also updated the docs at https://docs.photoprism.org/en/latest/setup/ - we changed the port and path in the Docker image, so this was outdated. How did you run / install PhotoPrism? Any issues?

deekerman commented

2026-02-19 23:02:19 -05:00

Author

Owner

@p80n commented on GitHub (Jul 3, 2019):

Updated to the latest and re-tested.

Query was actually still taking about the same time to run. I did some digging and narrowed it down to the join on photos_labels. Did some more digging and saw that while photo_id is indexed as part of the primary key on that table
PRIMARY KEY (`label_id`,`photo_id`),
I don't think the actual join
LEFT JOIN photos_labels ON photos_labels.photo_id = photos.id
is able to utilize the index, since label_id is not included.

So I added another index
KEY `idx_photos_id` (`photo_id`)
and now that query runs in about 0.5 seconds :)
I also tried reversing the order in the primary key. That also gets the query working, but I wasn't sure if there could be impacts to other bits of the code (i.e., that might query that table by just label_id).

For your second question, I actually run this in a kubernetes cluster. I have several small machines, so for me it's helpful to be able to run Photoprism on one, database on another. But I've just been translating your docker-compose to kube yaml no prob.
Thanks for the heads-up though - port change would've had me scratching my head for a bit otherwise.

@p80n commented on GitHub (Jul 3, 2019): Updated to the latest and re-tested. Query was actually still taking about the same time to run. I did some digging and narrowed it down to the join on `photos_labels`. Did some more digging and saw that while `photo_id` is indexed as part of the primary key on that table ``` PRIMARY KEY (`label_id`,`photo_id`),``` I don't think the actual join ```LEFT JOIN photos_labels ON photos_labels.photo_id = photos.id``` is able to utilize the index, since `label_id` is not included. So I added another index ``` KEY `idx_photos_id` (`photo_id`)``` and now that query runs in about 0.5 seconds :) I also tried reversing the order in the primary key. That also gets the query working, but I wasn't sure if there could be impacts to other bits of the code (i.e., that might query that table by just `label_id`). For your second question, I actually run this in a kubernetes cluster. I have several small machines, so for me it's helpful to be able to run Photoprism on one, database on another. But I've just been translating your docker-compose to kube yaml no prob. Thanks for the heads-up though - port change would've had me scratching my head for a bit otherwise.

deekerman commented

2026-02-19 23:02:19 -05:00

Author

Owner

@lastzero commented on GitHub (Jul 3, 2019):

Thank you, that was very helpful! Indeed, we need to be able to query the table both ways. I've just updated the model accordingly. Will be available as Docker image ~~when we merge into master next time~~ in about one hour. Build is running on TravisCI:

https://travis-ci.org/photoprism/photoprism/builds/553887037

@lastzero commented on GitHub (Jul 3, 2019): Thank you, that was very helpful! Indeed, we need to be able to query the table both ways. I've just updated the model accordingly. Will be available as Docker image ~~when we merge into master next time~~ in about one hour. Build is running on TravisCI: https://travis-ci.org/photoprism/photoprism/builds/553887037

deekerman commented

2026-02-19 23:02:19 -05:00

Author

Owner

@lastzero commented on GitHub (Jul 3, 2019):

@p80n Would be amazing if you share your kube yaml and any other advice for running this on Kubernetes... for example in our Wiki: Simply create a new page "Kubernetes" and add a link to it directly under Distribution on the main page

@lastzero commented on GitHub (Jul 3, 2019): @p80n Would be amazing if you share your kube yaml and any other advice for running this on Kubernetes... for example in our Wiki: Simply create a new page "Kubernetes" and add a link to it directly under [Distribution](https://github.com/photoprism/photoprism/wiki/Distribution) on the main page

deekerman commented

2026-02-19 23:02:19 -05:00

Author

Owner

@p80n commented on GitHub (Jul 3, 2019):

Tested it out on a fresh DB - works great 👍 Thanks!

I'll take a look at making the kube stuff I have generic and see if I can get something posted.

@p80n commented on GitHub (Jul 3, 2019): Tested it out on a fresh DB - works great 👍 Thanks! I'll take a look at making the kube stuff I have generic and see if I can get something posted.

deekerman referenced this issue

2026-02-19 23:02:58 -05:00

Idea: Shared Albums #142

deekerman referenced this issue

2026-02-19 23:07:41 -05:00

Centralized account management missing (Active Directory / LDAP) #379

deekerman referenced this issue

2026-02-19 23:08:07 -05:00

Hidden settings still accessible through URL route #394

deekerman referenced this issue

2026-02-19 23:09:22 -05:00

Sharing: Configure sharing options like upload / edit/view metadata / views #458