SQL Joins performed without indexes #1519

New issue

Closed

opened 2026-02-20 00:14:11 -05:00 by deekerman · 1 comment

deekerman commented

2026-02-20 00:14:11 -05:00

Owner

Originally created by @djjudas21 on GitHub (Aug 6, 2022).

1. What is not working as documented?

Please be as specific as possible and explain what part of the software is not working as documented. "No photos found" would not be detailed enough. Also, never report known issues or features not yet implemented as bugs. Thank you!

I am using the PhotoPrism with the MariaDB database backend, and performance of the app is sometimes slow. I decided to run mysqltuner against MariaDB to check configuration. It warned that there are some JOINs being performed without an index.

[!!] Joins performed without indexes: 2460

Unfortunately it does not say which query/queries use a JOIN without an index, but I will try and enable query logging.

2. How can we reproduce it?

Steps to reproduce the behavior:

Run PhotoPrism
Import photos
Browse photos
Leave MariaDB running at least 24 hours to gather metrics
Run mysqltuner

Photos: ~65000, mostly JPG
Thumbnail quality: 85%
Max thumbnail size: 1920px

3. What behavior do you expect?

I expect all JOINs to use indexed columns

4. What could be the cause of your problem?

A missing index on a column. I read #1811 where you improved the SQL queries and added indexes, but maybe there are more JOINs that need indexes.

As a workaround I can follow mysqltuner's advice:

set join_buffer_size (> 256.0K, or always use indexes with JOINs)

5. Can you provide us with example files for testing, error logs, or screenshots?

It is not possible to make MariaDB show you the contents of JOINs without indexes.

6. Which software versions do you use?

(a) PhotoPrism Architecture & Build Number: 220728-jammy on AMD64

(b) Database Type & Version: MariaDB

(d) Browser Types & Versions: Chrome

(e) Ad Blockers, Browser Plugins, and/or Firewall Software? No

7. On what kind of device is PhotoPrism installed?

This is especially important if you are reporting a performance, import, or indexing issue. You can skip this if you're reporting a problem you found in our public demo, or if it's a completely unrelated issue, such as incorrect page layout.

(a) Device / Processor Type: 3x nodes, each with Intel i5-6500T

(b) Physical Memory & Swap Space in GB: 3x nodes, each with 16GB RAM and swap disabled

(c) Storage Type: Photos are on network storage backed by HDD. Metadata, cache and MariaDB data are on network storage backed by SSD.

(d) Anything else that might be helpful to know? This is a 3x node Kubernetes cluster. PhotoPrism and MariaDB are configured to run on different nodes in the cluster so they can both consume max resources.

8. Do you use a Reverse Proxy, Firewall, VPN, or CDN?

My client PC is on the same wired LAN as the Kubernetes cluster that is running PhotoPrism. Kubernetes is using NGINX Ingress as a reverse proxy.

Originally created by @djjudas21 on GitHub (Aug 6, 2022). #### 1. What is not working as documented? Please be as specific as possible and explain what part of the software is not [working as documented](https://docs.photoprism.app/). "No photos found" would not be detailed enough. Also, never report [known issues](https://docs.photoprism.app/known-issues/) or [features not yet implemented](https://github.com/photoprism/photoprism/issues) as bugs. Thank you! I am using the PhotoPrism with the MariaDB database backend, and performance of the app is sometimes slow. I decided to run `mysqltuner` against MariaDB to check configuration. It warned that there are some JOINs being performed without an index. ``` [!!] Joins performed without indexes: 2460 ``` Unfortunately it does not say which query/queries use a JOIN without an index, but I will try and enable query logging. #### 2. How can we reproduce it? Steps to reproduce the behavior: 1. Run PhotoPrism 2. Import photos 3. Browse photos 4. Leave MariaDB running at least 24 hours to gather metrics 5. Run `mysqltuner` Photos: ~65000, mostly JPG Thumbnail quality: 85% Max thumbnail size: 1920px #### 3. What behavior do you expect? I expect all JOINs to use indexed columns #### 4. What could be the cause of your problem? A missing index on a column. I read #1811 where you improved the SQL queries and added indexes, but maybe there are more JOINs that need indexes. As a workaround I can follow `mysqltuner`'s advice: ``` set join_buffer_size (> 256.0K, or always use indexes with JOINs) ``` #### 5. Can you provide us with example files for testing, error logs, or screenshots? It is not possible to make MariaDB show you the contents of JOINs without indexes. #### 6. Which software versions do you use? (a) PhotoPrism Architecture & Build Number: 220728-jammy on AMD64 (b) Database Type & Version: MariaDB (c) Operating System Types & Versions: Kubernetes (d) Browser Types & Versions: Chrome (e) Ad Blockers, Browser Plugins, and/or Firewall Software? No #### 7. On what kind of device is PhotoPrism installed? This is especially important if you are reporting a performance, import, or indexing issue. You can skip this if you're reporting a problem you found in our public demo, or if it's a completely unrelated issue, such as incorrect page layout. (a) Device / Processor Type: 3x nodes, each with Intel i5-6500T (b) Physical Memory & Swap Space in GB: 3x nodes, each with 16GB RAM and swap disabled (c) Storage Type: Photos are on network storage backed by HDD. Metadata, cache and MariaDB data are on network storage backed by SSD. (d) Anything else that might be helpful to know? This is a 3x node Kubernetes cluster. PhotoPrism and MariaDB are configured to run on different nodes in the cluster so they can both consume max resources. #### 8. Do you use a Reverse Proxy, Firewall, VPN, or CDN? My client PC is on the same wired LAN as the Kubernetes cluster that is running PhotoPrism. Kubernetes is using NGINX Ingress as a reverse proxy.

deekerman

2026-02-20 00:14:11 -05:00

closed this issue
added the
incomplete
label

deekerman commented

2026-02-20 00:14:12 -05:00

Author

Owner

@lastzero commented on GitHub (Aug 6, 2022):

Depends on the query and table size. There is no law that says you must always use an index for everything, so it's not a bug as such. Please find out what specific query is slow and go through our Troubleshooting Checklists before opening a BUG REPORT. For example: https://docs.photoprism.app/getting-started/troubleshooting/performance/

Please also remove placeholders for readability. Thank you! ❤️

@lastzero commented on GitHub (Aug 6, 2022): Depends on the query and table size. There is no law that says you must always use an index for everything, so it's not a bug as such. Please find out what specific query is slow and go through our Troubleshooting Checklists before opening a BUG REPORT. For example: https://docs.photoprism.app/getting-started/troubleshooting/performance/ Please also remove placeholders for readability. Thank you! ❤️