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

[dannns]

Build 231128-f48ff16ef

Loading the library on first load and when scrolling down takes a few seconds.

I noticed that the query that takes a long time is the query below. Which on the first check was taking 4.6s. Trying to break it down, I ran the last part where it has a nested SELECT query and noticed that in itself that was taking 1.763 seconds and returning nothing. An index for this will pretty much have only one entry. So I tried adding the index for file_error and the sub-query execution was reduced to 0.002 seconds and the total execution time to almost half. Please see the SQL commands below for reference.

I think fixing this would apply to the preview branch too.

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 (3.424 sec)

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

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         |      371364 |     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         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_photo_taken_at  |            1 | photo_taken_at  | A         |      371364 |     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         |       10315 |     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         |          32 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_deleted_at      |            1 | deleted_at      | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_missing_root    |            1 | file_missing    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_missing_root    |            2 | file_root       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_published_at    |            1 | published_at    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
18 rows in set (0.001 sec)

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

MariaDB [photoprism]> SELECT photo_id FROM files WHERE file_primary = 1 AND (file_missing = 1 OR file_error <> '');
Empty set (0.002 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.932 sec)

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         |      371364 |     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         |      371364 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_photo_taken_at  |            1 | photo_taken_at  | A         |      371364 |     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         |       10315 |     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         |          32 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_deleted_at      |            1 | deleted_at      | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_missing_root    |            1 | file_missing    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_missing_root    |            2 | file_root       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_published_at    |            1 | published_at    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| files |          1 | idx_files_file_error      |            1 | file_error      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
19 rows in set (0.001 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.

[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.

[lastzero]

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

[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]

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! 🎉