Optimizing Speed Issues - A different approach

[trez]

I used 4images on a heavy visited site (almost 25.000 active members), and almost 50 MOD's here from the forum. If you are installing MOD's there will be at some point serious speed and load issues, no matter what.

If you are just about to launch your 4images website, than this thread can really help you avoid sped problems in near future. If you are already using 4images, and have issues than applying my way would be a little more difficult (but not impossible) for sure.

This is just a starter, I will update this thread every few weeks with more details.


1) Avoid *count anywhere you can!

A lot of mods are using this, mainly for paging and user statistics. Okay, we can't get rid of the paging, but we can do something about the statistics.
For example, you want to show how much images a user has in his lightbox. If we use the *count method we would use something like this in our member.php

    $sql = "SELECT *
         FROM ".LIGHTBOXES_TABLE."
         WHERE ".get_user_table_field("", "user_id")." = ".$user_row['user_id'];
     $user_lightbox_info = $site_db->query_firstrow($sql);
     $num_rows_all = 0;
     if (!empty($user_lightbox_info['lightbox_image_ids'])) {
     $image_id_sql = str_replace(" ", ",", trim($user_lightbox_info['lightbox_image_ids']));
     $sql = "SELECT COUNT(image_id) AS images
          FROM ".IMAGES_TABLE."
          WHERE image_active = 1 AND image_id IN ($image_id_sql)";
     $result = $site_db->query_firstrow($sql);
     $num_rows_all = $result['images'];
    }

Basically, this query searches every entry in your lightbox-table for a specific user_id. On every profile visit! On every member! Imagine having 100.000 entries in your lightbox table - server load will rise to 20-30, even on a dedicated machine.

So that example gives us two choices to solve the problem:

1) Using cache
2) Using smarter coding

Using the cache is okay, but that really doesn't solve the queries since they are generated once every X minutes/hours/days.

Okay, so a smarter approach would be to create a field (INT9) in your members table, and let's say calling it "user_count_lightbox". That field will contain the value of ... yes, the total count value of images that user has in his lightbox.

So, in order to get a correct value, we have to add a sql everywhere where the lightbox is being updated or modified so that every time the user adds/removes an image from his lightbox the count in the table field we created adds 1 or subtracts 1.

So in this example we have to add some code in functions.php , in the function "function add_to_lightbox($id) {".

It looks now like this:

Code: [Select]

function add_to_lightbox($id) {

  global $user_info, $site_db;
  $id = intval($id);
  if (!$id) {
    return false;
  }
 
  $lightbox_ids = $user_info['lightbox_image_ids'];
  $lightbox_array = explode(" ", $lightbox_ids);
  if (!in_array($id, $lightbox_array)) {
    $lightbox_ids .= " ".$id;
  }
  $user_info['lightbox_image_ids'] = trim($lightbox_ids);
  $user_info['lightbox_lastaction'] = time();
 
  $sql = "UPDATE ".LIGHTBOXES_TABLE."
          SET lightbox_lastaction = ".$user_info['lightbox_lastaction'].", lightbox_image_ids = '".$user_info['lightbox_image_ids']."'
          WHERE user_id = ".$user_info['user_id'];


//thats our code  
   $site_db->query($sql);  
    $sql = "UPDATE ".USERS_TABLE."
      SET user_count_lightbox=user_count_lightbox+1 WHERE user_id = ".$user_info['user_id'];
//end
 
 
return ($site_db->query($sql)) ? 1 : 0;

}


For the next function we have to be more careful, because we are subtracting, so the line would look like this:

Code: [Select]

    $sql = "UPDATE ".USERS_TABLE."
      SET user_count_lightbox=user_count_lightbox-1 WHERE user_count_lightbox> 0 AND user_id = ".$user_info['user_id'];


The only thing left would be registering our new field in the member.php like this

Code: [Select]

"user_count_lightbox" => $user_row['user_count_lightbox'],

And now we can use the tag {user_count_lightbox} to show how much pictures someone has without counting them every time the profile is being opened
That will save a lot of resources, and your site will load faster.

That example can be appended on almost every mod that uses "counting on the fly". Just think while you are installing it.

That's for now, I hope this is of some help since it's critical if you expect a lot of visitors. 

[V@no]

There is one issue with your method: when an images deleted the lightbox count for each member who had that image in their lightbox will needs to be updated. And the way 4images handles lightboxes it will be pretty much scanning every single lightbox one by one.

[Rembrandt]

Here is a other Solution:

1.) search in member.php section "($action == "showprofile")":

"user_id" => $user_row['user_id'],

insert above:

"user_count_lightbox" => (!empty($user_row['lightbox_image_ids']))? (count(explode(" ",$user_row['lightbox_image_ids']))):"",

2.) search in includes/functions.php in "function get_user_info($user_id = 0)":

    $sql = "SELECT *
            FROM ".USERS_TABLE."
            WHERE ".get_user_table_field("u.", "user_id")." = $user_id";

and replace:

  $sql = "SELECT u.*,l.*
          FROM ".USERS_TABLE." u
          LEFT JOIN ".LIGHTBOXES_TABLE." l ON (l.user_id = u.user_id)
          WHERE ".get_user_table_field("u.", "user_id")." = $user_id";


I think this is a better Solution...

mfg Andi

[trez]

Guys,

it's just a theoretical example, not a how-to. Maybe I haqd to use an other example because you are right v@no, when a whole lightbox gets deleted we have queries again. Anyway

Rembrandt, it's better then the first code I posted but it uses again a query. The purpose of this topic is to use less queries : )

[Rembrandt]

...The purpose of this topic is to use less queries : )

*huh* you have two queries more, my solution has no additional query.
The query in "function get_user_info" will be called, as soon as they enter the user profile.

mfg Andi

[trez]

Well they are not in the members profile, that was the main goal. More people are browsing profiles than adding an image to favorites. I am not saying that that method is perfect, but it works for me