Large zenphoto database

RichardB

website: https://graves.eggsa.org/
contains 11 individual zenphoto instances

Still in process of finding out why our system slows down badly from time to time.

My support has pointed out that a number of slow MySQL queries are reported, eg:

use graveseggsa_dbnatal;
SELECT EXIFOrientation
FROM graves_images AS i
JOIN graves_albums AS a ON i.albumid = a.id
WHERE 'PIETERMARITZBURG-Urban/Mountain-Rise/H-Surnames/IMG_9819.jpeg' = CONCAT(a.folder,'/',i.filename);

Query_time: 34.45s Rows_examined: 452425

would I be right in thinking the above was part of the process of changing orientation of an image?

our reason to change the image orientation is that, having imported the data from an old Menalto Gallery database, many of the images are wrongly orientated and need re-orientation.

the Natal database (graveseggsa_dbnatal) has 8,129 albums
and approx 112,000 images

Question 1: why so many rows? (Rows_examined: 452425)
Question 2: would creating an index on images orientation speed things up, do you think?

Thank you,
Richard

acrylian

would I be right in thinking the above was part of the process of changing orientation of an image?

Yes, that query is from the function getImageRotation() from funtions-image.php. That function is used when a cached image is generated so the resized and/or cropped images have the right orientation on display.

Perhaps the concat part is slowing it down. We could use PHP to extract album name/path and image file name from the image path before doing the actual query so we could do a simpler WHERE album = '' AND filemane = "" type of query instead of concat. Maybe that is faster.

No idea where these many rows come from. Have you looked into your database tables that they are actually really have the right rows and not accidentally storing some "garbage"? Maybe some file system permission issues resulted in duplicates or something? Just a wild guess of course.