main gallery page - query sorting entire photos table

[DST Daddy]

My gallery stats (which does take several minutes to load)
www.digishoptalk.com

Current Version 7.01
Registered Users 26,185
Comments/Ratings 6,946,951
Image Space Used 87,460.1 mb
Total Photos 823,614
Number of Albums 3980
Photos in Albums 100641
Posts in Albums 884582
ECards in Queue 0
Awaiting Approval 0
Total Views 29,766,770
Total disk space used by the DATA directory 102,524.94 mb


on my main gallery page (traditionally index.something) query number 12 basically looks at my entire photos table. Granted the query does limit to 33, but because the order by exists, sql orders the entire table and then returns the first 33 results.

Content visible to verified customers only.

the two items in the where clause probably won't make very good indexes because approved isn't going to vary very much, and I have nearly a 800k photos where storecat=0

Anything I can do?

[Chuck S]

well not anything I could think of off the top of my head unless you further isolate the query. Meaning alter it to query only photos from a specific category blah blah blah.

I mean we have imposed a limit on the number of results actually returned which is the only thing we can do.

Basically the query above is saying return the newest 33 results

whats your actual query time. I mean here is a site that has one million photos its all about server setup when you get that big

Main Index - Trackpads Gallery

[DST Daddy]

What kind of server configuration would you suggest? we are running a 2 server setup, one web, and one database. Each machine is multi core, and 8 gigs of ram.

Trackpads does have a lot of photos, but their activity rate is much lower. We have 33 views per photo, they have 1.7 views per.

I have identified a few queries that are doing more then they probably need to do, can you please let me know what pages I might find these queries under? I watched the show full processlist in mysql for about 10 mins and have pulled in a few queries that seem to be taking a long time to run.

This one is pulling back all basically all photos in the gallery

Content visible to verified customers only.

again, pulling back all photos in the gallery

Content visible to verified customers only.

even with the limit directive applied on the above 2 queries, the database is still forced to work through all records before it can apply the limit.

Content visible to verified customers only.

I know that more recent versions of mysql support more advanced sql like subqueries... perhaps there could be some option to define the version of mysql that is being run and utilize some of the more advanced options in some of the queries.

thanks

[Chuck S]

The first query is in showgallery however we dont specifically query showgallery like that. That query would come from running showgallery.php without a category variable. There is no active link in our latest code that does that.

Second query showphoto or showframe the next previous images you can turn those off. I beleive that is listed under the performance FAQ in the faq forum

I dont know why you posted the third query thats your category query in pp-inc.php to grab all your categories and info.

This comes down to a more precise conversation on mysql and how it works not so much with the queries. We place options to turn something on or off. For example as I previous talked about the recent photos. We can place a limit so not all results are printed that is good. However that query has no specific limits because of how its designed.

SELECT id,user,userid,cat,storecat,title,ismature,bigimage,height,width FROM pp_photos WHERE approved=1 AND storecat=0 ORDER BY date DESC LIMIT 33

There are VERY FEW queries that have to do a full table scan. The only ones I know of are the recent photos or next previous image previews. Anything else is very specific in the info returned. Both of these options can be turned off. The problem the for instance the recent photos. You want to show the latest 8 images. So we query for say 33 to limit the results which saves some processing time but you still need to scan the table. The problem is with that specific feature and what it is suppose to do there is nothing to really limit the query.

its not like your going to say WHERE cat=501 and userid=2 in that query because that sure would limit the scan but at the same time it would not return the latest 8 true photos uploaded to your gallery.

I wish there was a way to really optimize certain things but we really put on off switches for a reason as some things will have a higher load.

I have been doing alot of reading on mysql optimization and its limitations. If at all possible you want to refine queries to be very very specific but some functionalities in the program you can not be specific.

[DST Daddy]

What would be the complications of only caching where cattype='c'?

I think a potential issue is that I have nearly 4000 categories, this makes the category cache really big... or the query to get all the categories and category properties is pretty hefty....

[Chuck S]

The category cache in photopost does not contain any personal albums hense the cache is ONLY where cattype='c'

[DST Daddy]

if I SELECT * FROM `pp_categories` there are 4042 records returned...

my pp_cache catcache field starts out like this a:4052:{i:0;a:52:{i:0;s:4:"1818";s:2:"id";s:4:"181 and the catcache field is SUPER big

[DST Daddy]

if I add a "where cattype = 'c'" to the main query in the update_catcache() function my catcache starts out a:72 which is the same number that I get if I select count(*) from pp_categories where cattype = 'c'

[Chuck S]

If you dont get any adverse actions then go for it but I would suppose here your photo and comment counts are not going to be the same etc etc because your not going to be including all those.

[DST Daddy]

So all categories (including user albums) were being cached?

Anything I can do to reduce the size of the cat props? cache?

[Chuck S]

The category cache is something a user can turn on or off. I can be of a help or a detremtent to your site depending on how large it is etc etc.

I am merely saying to grab the proper counts etc that are shown on the cats block one needs to pass and access that information.

You can modify your code as you wish but I am just saying your category counts etc are not going to include data from those personal albums which may be relevant.

Personally I would just do some tests on load times with or without the cache and choose which one is faster.

PhotoPost Performance FAQ

Here was a faq Michael wrote up on this and things to turn on or off.

[DST Daddy]

thanks.... I've updated the code to not cache user albums, and load times are significantly faster. I believe it has to do with the query that pulls the cache from the DB. With the cache at 72 records vs 4000, the data can be returned to php much faster...

[Chuck S]

Yep glad it works for you