Hello all,
Certain SQL queries take a very long time to execute and were flagged by mysql for being too slow, which makes our gallery painfully slow.
Ex:
# Time: 160916 15:28:57
# User@Host: polyphoto[polyphoto] @ localhost [] Id: 548116
# Query_time: 108.693292 Lock_time: 0.000206 Rows_sent: 82265 Rows_examined: 349513
SET timestamp=1474054137;
SELECT DISTINCT id FROM piwigo_images
INNER JOIN piwigo_image_category ON id = image_id
WHERE (category_id NOT IN (15,16,17,18,138,182,183,184,185,186,187,188,270,273,275,326,424,425,426,427,483,484,485,486,526,527,528,529,698,699,743,805,806,807,808,856,857,858,859,860,861,862,863,864,865,866,946,947,948,949,1054,1055,1056,1057,1103,1174,1186,1187,1188,1189,1196,1239,1244,1263,1327,1336,1337,1341,1346,1348,1372,1398,1419,1423,1452,1497,1498,1503,1506,1509,1544,1615,1613,1614,1654,1655,1662,1664,695,1217,1218,1541) AND level<=0)
AND date_creation IS NOT NULL
ORDER BY date_creation DESC, file ASC, id ASC, date_available DESC;
We've been having trouble for a while now but it's only gotten worse. It often takes upwards of 5 minutes to open an album or a single image, and the administrative section is even slower.
Any help would be immensely appreciated as the website is basically inusable at the moment (though it runs well from time to time).
Sincerely,
Offline
Best guess: move to a better hosting plan with faster metal and less shared hosting (as "runs well from time to time" may indicate).
Offline