The site is 5 months old, already there are 240,000 comments and 45,000 photos. Assuming no growth (which isn't likely), these queries which are sorting through 240k rows and 45k rows now will be sorting through 550k rows and 108k rows in another 7 months.
I would suggest trying to make sure that you are using the categories efficiently; one category with > 50k images isn't very efficient. We are looking at ways to improve some of our queries for larger sites, but PhotoPost was designed with many features for the large majority of our customers; larger sites, to be more efficent, need less options.
For example, we could easily reduce the size of queries if we didn't allow categories to be sorted - just limiting the display to the photo date would allow us to limit our queries based on the date of the photo being displayed - but when you allow the flexibility of sorting a gallery and then having that sort "stick" while viewing photos, well, then its going to use more resources.
It's not that PhotoPost can't scale to large systems, it's that you need to manage your options accordingly and possibly even look at pulling back on some of the options to allow even greater scalability.
Please do not PM me for support or sales questions. Thank you for your understanding.
I finally got around to updating photopost, am now running 5.62.
several queries run VERY regularly which a month ago only had to sort through 240,000 records, now the queries sort through 300,000 records.
doing a "show full processlist" on my mysql server I find 4 instances of this query
"SELECT username,id,date,photo FROM pp_comments ORDER BY date DESC"
which selected 305,773 records and sorts them by date (a field without an index)...
another query which comes up quite regularly is "SELECT id,user,userid,date FROM pp_photos ORDER BY date DESC" or a variation which looks similar to "SELECT id,user,userid,date FROM pp_photos ORDER BY date DESC" these queries sort through 50,000 + records each, again on date... which is not in indexed field.
what should the next step be? (we are working on getting things broken down into multiple categories)
I have turned on the slow queries log in mysql, and am seeing lots of queries returning over 50k records and over 290k records
I know that there are not any pages which show 50k records or 290k records on them..... can't these queries be limited to return a smaller record set? Most of what is being returned is being disregarded by php anyway.