|
|
| Author |
Message |
JLA
Not So New User
Joined: 03 Dec 2004
Posts: 73
|
 Posted: March 20th 2007, 8:04 pm Post subject: Great idea to speed SMARTOR Photo Gallery up |
 |
|
We have a photo gallery with over 270,000 pictures with a capacity of 10 million pictures
We have noticed that the Smartor Photo Album is very slow with all the pictures we have so it has required stripping quite a bit of functionality out that we were not using in the 1st place. We have posted over at the Smartor community and had a general lack of response or desire to work with us to improve the photo album. We have done some extensive work on it as well and now are hoping to open up discussion here on some ideas and perhaps we can work on a few solutions.
The 1st we have is this.
http://smartor.is-root.com/viewtopic.php?t=18774
As it stands at the end of that thread, this is our idea to deal with the following problem queries in the photo album
| Code:
|
*
+----------------------------------------------------------
| Build Categories Index
+----------------------------------------------------------
*/
$sql = "SELECT c.*, COUNT(p.pic_id) AS count
FROM ". ALBUM_CAT_TABLE ." AS c
LEFT JOIN ". ALBUM_TABLE ." AS p ON c.cat_id = p.pic_cat_id
WHERE cat_id <> 0
GROUP BY cat_id
ORDER BY cat_order ASC";
if( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not query categories list', '', __LINE__, __FILE__, $sql);
}
|
Our idea is as follows
Counting the pictures in each category has to be the worst thing ever for this mod. Not sure why it was done this way, but it is the hugest waste of time and resources for this mod.
We propose the following:
1. In the album_cat table, we create a field called cat_count.
2. In this field is stored a value which equals the amount of pictures in that particular category.
3. When a picture is added to the photo album, in the insert into database we tell it to add +1 to this value (cat_count) for the particular category where the picture is located.
4. When a picture is deleted from the photo album we tell it to subtract -1 from the value in cat_count
5. In the queries in album index, album cat, etc we substitute a query to just pull the value from cat_count instead of counting all the rows of pictures for a particular category
6. In the photo album admin, we create a "resync" function that will do a LONG COUNT like the existing count which will go out and manually count each picture for each category and then update the cat_count value in the album_cat table. This will help if things ever get messed up.
What do you all think about this option? Believe it will make this MUCH faster.
_________________
http://www.jlaforums.com |
|
| Back to top |
|
 |
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
| Posted: March 21st 2007, 12:44 pm Post subject: |
|
|
Yeah, that will give you something of a speed increase. Counting the number dynamically is "easier" on the author, but it's rough on speed and resources. Your proposed changes will be an improvement, especially in your situation. That's how I would do it if I was writing the hack.
_________________
Phantasy Star: Fringes of Algo
70+ Listings @ phpBBHacks.com |
|
| Back to top |
|
|
JLA
Not So New User
Joined: 03 Dec 2004
Posts: 73
|
| Posted: March 21st 2007, 1:09 pm Post subject: |
|
|
Thats great to finally have some input. Would anyone here like to work with us on this and a few other changes (Team Effort) that should bring great improvement to the SMARTOR album?
We have tried over at the Smartor community but there has been very little give and alot of demand for take and absolutely no team effort type attitude.
_________________
http://www.jlaforums.com |
|
| Back to top |
|
|
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
|
| Back to top |
|
|
JLA
Not So New User
Joined: 03 Dec 2004
Posts: 73
|
| Posted: March 22nd 2007, 10:00 pm Post subject: |
|
|
Thanks again Thoul
We had success with this work today
We found one of the biggest weaknesses of the Smartor Photo Album is the COUNTING that it does of pictures in categories. If you only have a few pictures in your gallery it is not very noticeable but when you get to the 270,000 picture mark as we are - you will find a whole different story.
Unfortunately no one over in the Smartor community was willing to contribute to a team effort in attempting to solve this problem and give this mod a MUCH NEEDED boost in performance. Also, we really didn't hear from anyone but you Thoul (and a BIG thank you for your input)
We went ahead and tackled this problem ourselves and it is now solved. .
What we did.
1. Removed all counting references and queries from album index and album cat
2. Told both of these pages to pull a total category count figure from a new field in the album cat table called cat total.
3. In album upload had it add +1 to this cat total field when uploading a picture
4. In album moderator and album delete had it subtract -1 from cat total when deleting a picture
5. Created a new script to run on occasion from the admin to manually count category by category all pictures and refresh cat total if the admin wishes to verify category total picture counts
This has made such a huge improvement and is worth it to those who wish to do the same.
This makes the Smartor Photo Gallery now scalable up to millions of pictures and beyond.
_________________
http://www.jlaforums.com |
|
| Back to top |
|
|
Afkamm
Well Known User
Joined: 20 Oct 2004
Posts: 238
Location: Scotland
|
|
| Back to top |
|
|
JLA
Not So New User
Joined: 03 Dec 2004
Posts: 73
|
| Posted: March 31st 2007, 8:07 am Post subject: |
|
|
| Afkamm wrote:
|
| Quote:
|
|
3. In album upload had it add +1 to this cat total field when uploading a picture
|
Only when the upload was successful, yes?
You should write your changes up and create some sort of add-on hack, then add it to the database. Good work. 
|
Yes, we added it after completion of successful picture uploading - same for the delete and move functions
_________________
http://www.jlaforums.com |
|
| Back to top |
|
|
JLA
Not So New User
Joined: 03 Dec 2004
Posts: 73
|
| Posted: May 30th 2007, 6:48 pm Post subject: |
|
|
Well we are now over 460,000 pictures in the gallery and it is running perfectly with the new code.
Thanks again for the comments
_________________
http://www.jlaforums.com |
|
| Back to top |
|
|
beggers
Well Known User
Joined: 11 Aug 2001
Posts: 177
Location: Las Vegas
|
| Posted: July 24th 2007, 12:43 am Post subject: |
|
|
| JLA wrote:
|
Well we are now over 460,000 pictures in the gallery and it is running perfectly with the new code.
Thanks again for the comments
|
That's good news. Actually, my biggest problem with the gallery has been the lack of management tools. For example, if a user's account gets deleted, his pictures remain in the gallery. There's also no feature to remove images that are x months old, for example. You're stuck with a bunch of junk.
With all those images in your gallery you must have developed some methods of pruning orphaned accounts/images. Any suggestions?
_________________
WWE Wrestling|Sports|Las Vegas |
|
| Back to top |
|
|
JLA
Not So New User
Joined: 03 Dec 2004
Posts: 73
|
| Posted: July 24th 2007, 7:07 am Post subject: |
|
|
| beggers wrote:
|
| JLA wrote:
|
Well we are now over 460,000 pictures in the gallery and it is running perfectly with the new code.
Thanks again for the comments
|
That's good news. Actually, my biggest problem with the gallery has been the lack of management tools. For example, if a user's account gets deleted, his pictures remain in the gallery. There's also no feature to remove images that are x months old, for example. You're stuck with a bunch of junk.
With all those images in your gallery you must have developed some methods of pruning orphaned accounts/images. Any suggestions?
|
We are now over 600,000 pictures and growing even faster. As we are not removing pictures within an age limit, we can't provide you any specific code, but you could write a simple php script that should do something like this:
1. Gather from the phpbb_album table all the picture information of all pictures that are older than a specific amount of days/date
2. Once it has this information it will 1st delete these pic id's from the phpbb album table by 1st deleting the row and then going to your upload and cache directory and deleting the actual file by using the file names that was recorded in the row for the file image name and thumnail image name.
As far as deleting user images, there is already a script over at smartor's site that deals with this and it works well. Since we retain all user images, we just move the user's images from the personal gallery to a specific other gallery by changing the cat id on the row for those pics when a user is deleted
Alot of things you do in phpbbadmin for a large amount of pics. I will give you an example. Say you want to search through your whole gallery for picture titles that contain the work SEX. Just go to phpbb admin and into the album table. Use the SEARCH to search the field pic_title for sex using the %like% and it will give you the results of all pictures that have this. Then EDIT that query and change the beginning to UPDATE `phpbb_album` and right before the where put SET `pic_cat_id` =(your new category number) to whatever category you want to move them to. You could also right this into the pic admin panel if you wanted as well. Many of the functions you might want could be done in this manner if you know what you are doing. Just be sure you have a backup of everything incase you mess up.
Best of luck
_________________
http://www.jlaforums.com |
|
| Back to top |
|
|
|
Forums
Home
Register
Profile
Log in to check your private messages
Log in
FAQ
Search
Contact
Us 
