adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-11-03 07:22:22 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

Please add index to piwigo_image.path field. #1105

New Issue
Closed
opened 2026-10-05 00:31:29 +02:00 by adam · 2 comments
adam commented 2026-10-05 00:31:29 +02:00
Owner
Copy link
Copy source

The table piwigo_images needs an index on the field path in order to remove table scans when looking for images that are locally synchronised. I looked at Mysql query performance on Mariadb and found lots of first row and table scan warnings in the performance logs, due to Select * from piwigo_images where path=''

My server is a quad core and each image would spike to 240% CPU load in Mysql when fetched by the browser at the API GET /_data/i/galleries/

https://github.com/Piwigo/Piwigo/blob/a641b9eb08650272b3bf8f9e9db92ffd3e529d1f/install/piwigo_structure-mysql.sql#L215

adding an index to the path field on this table resulted in a 200% reduction in load, and a massive speed up in image retrieval.

The table piwigo_images needs an index on the field path in order to remove table scans when looking for images that are locally synchronised. I looked at Mysql query performance on Mariadb and found lots of first row and table scan warnings in the performance logs, due to Select * from piwigo_images where path='<absolute path of local image>' My server is a quad core and each image would spike to 240% CPU load in Mysql when fetched by the browser at the API GET /_data/i/galleries/<image name> https://github.com/Piwigo/Piwigo/blob/a641b9eb08650272b3bf8f9e9db92ffd3e529d1f/install/piwigo_structure-mysql.sql#L215 adding an index to the path field on this table resulted in a 200% reduction in load, and a massive speed up in image retrieval. <!-- github-sync-id:770192915 -->
adam closed this issue 2026-10-05 00:31:29 +02:00
adam commented 2026-10-05 00:31:30 +02:00
Author
Owner
Copy link
Copy source

Hi @andyleadbetter

I've made some test with a 1 million lines piwigo_images table. On average, the select ... where path=... query takes 0.900 seconds on my rather slow old computer. It's absolutely huge.

With the index ALTER TABLE piwigo_images ADD INDEX images_i7 (path); the same queries take less than 0.001 second. That's 900 times faster.

This index will obviously improve performances with big images tables.

Hi @andyleadbetter I've made some test with a 1 million lines `piwigo_images` table. On average, the `select ... where path=...` query takes 0.900 seconds on my rather slow old computer. It's absolutely huge. With the index `ALTER TABLE piwigo_images ADD INDEX images_i7 (path);` the same queries take less than 0.001 second. That's 900 times faster. This index will obviously improve performances with big images tables. <!-- github-comment-id:748100834 -->
adam commented 2026-10-05 00:31:31 +02:00
Author
Owner
Copy link
Copy source

The only problem I see is the time to create this index.

mysql> ALTER TABLE piwigo_images ADD INDEX `images_i7` (`path`);
Query OK, 960171 rows affected (32.45 sec)
Records: 960171  Duplicates: 0  Warnings: 0

32 seconds in huge during the upgrade procedure. I hope the upgrade page won't finish as timed out.

The only problem I see is the time to create this index. ``` mysql> ALTER TABLE piwigo_images ADD INDEX `images_i7` (`path`); Query OK, 960171 rows affected (32.45 sec) Records: 960171 Duplicates: 0 Warnings: 0 ``` 32 seconds in huge during the upgrade procedure. I hope the upgrade page won't finish as timed out. <!-- github-comment-id:748104440 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#1105