Performance: Add index for `files.file_error` to reduce query time about photoprism

[dannns]

MariaDB:
Server version: 10.6.17-MariaDB-1:10.6.17+maria~ubu2004 mariadb.org binary distribution

Running in docker with a volume mounted to the host on an SSD (SATA) drive.

[dannns]

Now with MariaDB 11. The difference is even more noticeable.

Before adding index:
First try: 2.867s
Second try: 2.793s

After adding index:
First try: 1.030s
Second try: 0.542s

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE photoprism;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [photoprism]> SHOW INDEXES FROM files;
+-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name                  | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| files |          0 | PRIMARY                   |            1 | id              | A         |      371364 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| files |          0 | uix_files_file_uid        |            1 | file_uid        | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          0 | idx_files_name_root       |            1 | file_name       | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          0 | idx_files_name_root       |            2 | file_root       | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          0 | idx_files_search_media    |            1 | media_id        | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          0 | idx_files_search_timeline |            1 | time_index      | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_photo_id        |            1 | photo_id        | A         |      185682 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_photo_id        |            2 | file_primary    | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_photo_uid       |            1 | photo_uid       | A         |      185682 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_photo_taken_at  |            1 | photo_taken_at  | A         |      185682 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_media_utc       |            1 | media_utc       | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_instance_id     |            1 | instance_id     | A         |        5087 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_file_hash       |            1 | file_hash       | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_file_main_color |            1 | file_main_color | A         |          16 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_deleted_at      |            1 | deleted_at      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_missing_root    |            1 | file_missing    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_missing_root    |            2 | file_root       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_published_at    |            1 | published_at    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_file_error      |            1 | file_error      | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
19 rows in set (0.001 sec)

MariaDB [photoprism]> DROP INDEX idx_files_file_error ON files;
Query OK, 0 rows affected (0.019 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [photoprism]> SELECT SUM(photo_type = 'video' AND photo_quality > -1 AND photo_private = 0) AS videos, SUM(photo_type = 'live' AND photo_quality > -1 AND photo_private = 0) AS live, SUM(photo_quality = -1) AS hidden, SUM(photo_type NOT IN ('live', 'video') AND photo_quality > -1 AND photo_private = 0) AS photos, SUM(photo_quality BETWEEN 0 AND 2) AS review, SUM(photo_favorite = 1 AND photo_private = 0 AND photo_quality > -1) AS favorites, SUM(photo_private = 1 AND photo_quality > -1) AS private FROM `photos`  WHERE (photos.id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> ''))) AND (deleted_at IS NULL) LIMIT 1;
+--------+------+--------+--------+--------+-----------+---------+
| videos | live | hidden | photos | review | favorites | private |
+--------+------+--------+--------+--------+-----------+---------+
|  10691 | 1442 |    294 | 165352 |  43389 |         5 |      18 |
+--------+------+--------+--------+--------+-----------+---------+
1 row in set (2.867 sec)

MariaDB [photoprism]> SELECT SUM(photo_type = 'video' AND photo_quality > -1 AND photo_private = 0) AS videos, SUM(photo_type = 'live' AND photo_quality > -1 AND photo_private = 0) AS live, SUM(photo_quality = -1) AS hidden, SUM(photo_type NOT IN ('live', 'video') AND photo_quality > -1 AND photo_private = 0) AS photos, SUM(photo_quality BETWEEN 0 AND 2) AS review, SUM(photo_favorite = 1 AND photo_private = 0 AND photo_quality > -1) AS favorites, SUM(photo_private = 1 AND photo_quality > -1) AS private FROM `photos`  WHERE (photos.id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> ''))) AND (deleted_at IS NULL) LIMIT 1;
+--------+------+--------+--------+--------+-----------+---------+
| videos | live | hidden | photos | review | favorites | private |
+--------+------+--------+--------+--------+-----------+---------+
|  10691 | 1442 |    294 | 165352 |  43389 |         5 |      18 |
+--------+------+--------+--------+--------+-----------+---------+
1 row in set (2.793 sec)

MariaDB [photoprism]> SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> '');
Empty set (1.742 sec)

MariaDB [photoprism]> SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> '');
Empty set (1.724 sec)

MariaDB [photoprism]> ALTER TABLE files ADD INDEX `idx_files_file_error` (`file_error`);
Query OK, 0 rows affected (2.186 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [photoprism]> SHOW INDEXES FROM files;
+-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name                  | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| files |          0 | PRIMARY                   |            1 | id              | A         |      371364 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| files |          0 | uix_files_file_uid        |            1 | file_uid        | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          0 | idx_files_name_root       |            1 | file_name       | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          0 | idx_files_name_root       |            2 | file_root       | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          0 | idx_files_search_media    |            1 | media_id        | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          0 | idx_files_search_timeline |            1 | time_index      | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_photo_id        |            1 | photo_id        | A         |      185682 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_photo_id        |            2 | file_primary    | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_photo_uid       |            1 | photo_uid       | A         |      185682 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_photo_taken_at  |            1 | photo_taken_at  | A         |      185682 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_media_utc       |            1 | media_utc       | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_instance_id     |            1 | instance_id     | A         |        5087 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_file_hash       |            1 | file_hash       | A         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_file_main_color |            1 | file_main_color | A         |          16 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_deleted_at      |            1 | deleted_at      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_missing_root    |            1 | file_missing    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_missing_root    |            2 | file_root       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_published_at    |            1 | published_at    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_file_error      |            1 | file_error      | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
19 rows in set (0.001 sec)

MariaDB [photoprism]> SELECT SUM(photo_type = 'video' AND photo_quality > -1 AND photo_private = 0) AS videos, SUM(photo_type = 'live' AND photo_quality > -1 AND photo_private = 0) AS live, SUM(photo_quality = -1) AS hidden, SUM(photo_type NOT IN ('live', 'video') AND photo_quality > -1 AND photo_private = 0) AS photos, SUM(photo_quality BETWEEN 0 AND 2) AS review, SUM(photo_favorite = 1 AND photo_private = 0 AND photo_quality > -1) AS favorites, SUM(photo_private = 1 AND photo_quality > -1) AS private FROM `photos`  WHERE (photos.id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> ''))) AND (deleted_at IS NULL) LIMIT 1;
+--------+------+--------+--------+--------+-----------+---------+
| videos | live | hidden | photos | review | favorites | private |
+--------+------+--------+--------+--------+-----------+---------+
|  10691 | 1442 |    294 | 165352 |  43389 |         5 |      18 |
+--------+------+--------+--------+--------+-----------+---------+
1 row in set (1.030 sec)

MariaDB [photoprism]> SELECT SUM(photo_type = 'video' AND photo_quality > -1 AND photo_private = 0) AS videos, SUM(photo_type = 'live' AND photo_quality > -1 AND photo_private = 0) AS live, SUM(photo_quality = -1) AS hidden, SUM(photo_type NOT IN ('live', 'video') AND photo_quality > -1 AND photo_private = 0) AS photos, SUM(photo_quality BETWEEN 0 AND 2) AS review, SUM(photo_favorite = 1 AND photo_private = 0 AND photo_quality > -1) AS favorites, SUM(photo_private = 1 AND photo_quality > -1) AS private FROM `photos`  WHERE (photos.id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> ''))) AND (deleted_at IS NULL) LIMIT 1;
+--------+------+--------+--------+--------+-----------+---------+
| videos | live | hidden | photos | review | favorites | private |
+--------+------+--------+--------+--------+-----------+---------+
|  10691 | 1442 |    294 | 165352 |  43389 |         5 |      18 |
+--------+------+--------+--------+--------+-----------+---------+
1 row in set (0.542 sec)

MariaDB [photoprism]> SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> '');
Empty set (0.000 sec)

MariaDB [photoprism]> SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> '');
Empty set (0.001 sec)

MariaDB [photoprism]> SELECT SUM(photo_type = 'video' AND photo_quality > -1 AND photo_private = 0) AS videos, SUM(photo_type = 'live' AND photo_quality > -1 AND photo_private = 0) AS live, SUM(photo_quality = -1) AS hidden, SUM(photo_type NOT IN ('live', 'video') AND photo_quality > -1 AND photo_private = 0) AS photos, SUM(photo_quality BETWEEN 0 AND 2) AS review, SUM(photo_favorite = 1 AND photo_private = 0 AND photo_quality > -1) AS favorites, SUM(photo_private = 1 AND photo_quality > -1) AS private FROM `photos`  WHERE (photos.id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> ''))) AND (deleted_at IS NULL) LIMIT 1;
+--------+------+--------+--------+--------+-----------+---------+
| videos | live | hidden | photos | review | favorites | private |
+--------+------+--------+--------+--------+-----------+---------+
|  10691 | 1442 |    294 | 165352 |  43389 |         5 |      18 |
+--------+------+--------+--------+--------+-----------+---------+
1 row in set (0.552 sec)

[lastzero]

Thanks! I'll look into it and add an index if needed to make the query run faster. For this, please also let us know what version of MariaDB and what type of storage you are using.

[lastzero]

Could you test for us if there is any difference in performance when using MariaDB 11.x?

[lastzero]

An updated preview build is now available on Docker Hub for you to test these changes:

• https://docs.photoprism.app/getting-started/updates/#development-preview
• https://docs.photoprism.app/release-notes/#development-preview

Any help with that is much appreciated! 🎉

[lastzero]

@dannns I tested it on my personal instance (since it has a lot of files), but I didn't notice any major change in performance... Indexing took 17 seconds before (without new files, so just scanning the folders and running maintenance checks). With the new version, the time it takes has slightly dropped to 16 seconds.

Note, however, that I don't have any "hidden" files in my library which I assume would be most affected by this change (since the query searches the index for broken files).

[dannns]

Strangely enough I don't see difference now either. The only thing, that I don't know if they influence in any way is that I've been making other optimizations. Like the update to mariadb 11, increased the --innodb-buffer-pool-size=2048MB, updating a few things in the docker compose file, and running things like faces audit -f and faces optimize.

Query wise there seems to still be a minor difference.

Before update (with my temporary index manually dropped.

MariaDB [photoprism]> SELECT SUM(photo_type = 'video' AND photo_quality > -1 AND photo_private = 0) AS videos, SUM(photo_type = 'live' AND photo_quality > -1 AND photo_private = 0) AS live, SUM(photo_quality = -1) AS hidden, SUM(photo_type NOT IN ('live', 'video') AND photo_quality > -1 AND photo_private = 0) AS photos, SUM(photo_quality BETWEEN 0 AND 2) AS review, SUM(photo_favorite = 1 AND photo_private = 0 AND photo_quality > -1) AS favorites, SUM(photo_private = 1 AND photo_quality > -1) AS private FROM `photos`  WHERE (photos.id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> ''))) AND (deleted_at IS NULL) LIMIT 1;
+--------+------+--------+--------+--------+-----------+---------+
| videos | live | hidden | photos | review | favorites | private |
+--------+------+--------+--------+--------+-----------+---------+
|  10691 | 1480 |    294 | 166173 |  44179 |         6 |      18 |
+--------+------+--------+--------+--------+-----------+---------+
1 row in set (0.711 sec)

MariaDB [photoprism]> SELECT SUM(photo_type = 'video' AND photo_quality > -1 AND photo_private = 0) AS videos, SUM(photo_type = 'live' AND photo_quality > -1 AND photo_private = 0) AS live, SUM(photo_quality = -1) AS hidden,
SUM(photo_type NOT IN ('live', 'video') AND photo_quality > -1 AND photo_private = 0) AS photos, SUM(photo_quality BETWEEN 0 AND 2) AS review, SUM(photo_favorite = 1 AND photo_private = 0 AND photo_quality > -1) AS favorites, SUM(photo_private = 1 AND photo_quality > -1) AS private FROM `photos`  WHERE (photos.id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> ''))) AND (deleted_at IS NULL) LIMIT 1;
+--------+------+--------+--------+--------+-----------+---------+
| videos | live | hidden | photos | review | favorites | private |
+--------+------+--------+--------+--------+-----------+---------+
|  10691 | 1480 |    294 | 166173 |  44179 |         6 |      18 |
+--------+------+--------+--------+--------+-----------+---------+
1 row in set (0.691 sec)

After update to preview image.

MariaDB [photoprism]> SELECT SUM(photo_type = 'video' AND photo_quality > -1 AND photo_private = 0) AS videos, SUM(photo_type = 'live' AND photo_quality > -1 AND photo_private = 0) AS live, SUM(photo_quality = -1) AS hidden,
SUM(photo_type NOT IN ('live', 'video') AND photo_quality > -1 AND photo_private = 0) AS photos, SUM(photo_quality BETWEEN 0 AND 2) AS review, SUM(photo_favorite = 1 AND photo_private = 0 AND photo_quality > -1) AS favorites, SUM(photo_private = 1 AND photo_quality > -1) AS private FROM `photos`  WHERE (photos.id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> ''))) AND (deleted_at IS NULL) LIMIT 1;
+--------+------+--------+--------+--------+-----------+---------+
| videos | live | hidden | photos | review | favorites | private |
+--------+------+--------+--------+--------+-----------+---------+
|  10691 | 1480 |    294 | 166173 |  44179 |         6 |      18 |
+--------+------+--------+--------+--------+-----------+---------+
1 row in set (0.573 sec)

MariaDB [photoprism]> SELECT SUM(photo_type = 'video' AND photo_quality > -1 AND photo_private = 0) AS videos, SUM(photo_type = 'live' AND photo_quality > -1 AND photo_private = 0) AS live, SUM(photo_quality = -1) AS hidden,
SUM(photo_type NOT IN ('live', 'video') AND photo_quality > -1 AND photo_private = 0) AS photos, SUM(photo_quality BETWEEN 0 AND 2) AS review, SUM(photo_favorite = 1 AND photo_private = 0 AND photo_quality > -1) AS favorites, SUM(photo_private = 1 AND photo_quality > -1) AS private FROM `photos`  WHERE (photos.id NOT IN (SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> ''))) AND (deleted_at IS NULL) LIMIT 1;
+--------+------+--------+--------+--------+-----------+---------+
| videos | live | hidden | photos | review | favorites | private |
+--------+------+--------+--------+--------+-----------+---------+
|  10691 | 1480 |    294 | 166173 |  44179 |         6 |      18 |
+--------+------+--------+--------+--------+-----------+---------+
1 row in set (0.547 sec)

Now when comparing pages load time, they appear to be the same. The see the photos page load time below when scrolling down the library.

Before update (with my temporary index manually dropped.

After update to preview image.

So it is kind of inconclusive. Maybe the index is better, but I cannot say that things changed much. I do feel like loading times are much better than before I started this investigation. Without telling her anything, my wife said herself that it feels much faster than before.

Thanks for the quick response. Hopefully this did fix something and made this great app a little bit better!

[dannns]

One small thing that I'm noticing after posting the screenshots is that the photos result is much smaller now in the preview version. Nice!

[lastzero]

If you increased the buffer size (as recommended in our troubleshooting guide), a temporary table or some other nasty fallback may have been used previously to run the query and reduce the amount of physical memory being used. It is generally not a good idea to add lots of indexes as this increases disk usage, slows down update queries and increases the likelihood of locks. There are often better ways to improve performance, e.g. by sorting or adding additional where conditions so that only a few rows need to be compared without the help of an index.