Author Topic: Caching "SELECT cat_id, COUNT(*)..." from global.php  (Read 21248 times)

0 Members and 1 Guest are viewing this topic.

Offline up23

  • Pre-Newbie
  • Posts: 5
    • View Profile
Caching "SELECT cat_id, COUNT(*)..." from global.php
« on: March 31, 2006, 01:44:17 PM »
I've been kind of stress testing 4images, and I've noticed that (with the cache turned on) the mysql query that takes the most time (>70%) is the lines:

Code: [Select]
  $sql = "SELECT cat_id, COUNT(*) AS num_images
          FROM ".IMAGES_TABLE."
          WHERE image_active = 1
          GROUP BY cat_id";
  $result = $site_db->query($sql);

from global.php. Is there any way to put the result of this in the cache to speed things up?

I believe the time increases linearly with the number of files. I'm running the mysql under xampps with windows xp and 70,000 files in the database. Category pages show up in 0.7 seconds with cache; this mysql query takes 0.55 seconds.

As a side note, I sped up the details.php page by commenting out the next/prev file section, and use include of global2.php where global2.php has the code above commented out. No the most finesse way, I know. Also, the "show new" feature seems to take a lot of resources, but then all 70,000 files in my test database were new.

So if anyone know how to cache the above code, please post it. Thanks.

Offline relu

  • Newbie
  • *
  • Posts: 44
    • View Profile
    • Pulsarmedia
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #1 on: January 15, 2009, 02:45:48 AM »
The same problem here...i search for this issue to resolve it but sometime the server load is so high and in the proceses this apear there.

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Global Moderator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #2 on: January 15, 2009, 03:22:49 AM »
Try this, in global.php replace
Code: [Select]
  $sql = "SELECT cat_id, COUNT(*) AS num_images
          FROM ".IMAGES_TABLE."
          WHERE image_active = 1
          GROUP BY cat_id";
  $result = $site_db->query($sql);

  while ($row = $site_db->fetch_array($result)) {
    $cat_cache[$row['cat_id']]['num_images'] = $row['num_images'];
  }
  $site_db->free_result();

With:
Code: [Select]
  $cat_cache_file = ROOT_PATH."data/.htcatcache"; //name like that should protect from accessing from web
  $cat_cache_data = "";
  $cached = 0;
  if (file_exists($cat_cache_file) && filemtime($cat_cache_file) > (time() - 10) && $cat_cache_data = file($cat_cache_file))
  {
    foreach($cat_cache_data as $data)
    {
      $data = explode(" ", $data);
      if (!isset($data[1]) || !isset($cat_cache[$data[0]]))
      {
        $cached = 0;
        break;
      }
      $cat_cache[$data[0]]['num_images'] = intval($data[1]);
      $cached = 1;
    }
  }
  if (!$cached)
  {
    $sql = "SELECT cat_id, COUNT(*) AS num_images
            FROM ".IMAGES_TABLE."
            WHERE image_active = 1
            GROUP BY cat_id";
    $result = $site_db->query($sql);

    while ($row = $site_db->fetch_array($result)) {
      $cat_cache[$row['cat_id']]['num_images'] = $row['num_images'];
      $cat_cache_data .= $row['cat_id']." ".$row['num_images']."\n";
    }
    $site_db->free_result();
    file_put_contents($cat_cache_file, trim($cat_cache_data));
  }

But I doubt it will improve performance...
P.S. PHP v5 or above required.
Your first three "must do" before you ask a question:
Please do not PM me asking for help unless you've been specifically asked to do so. Such PMs will be deleted without answer. (forum rule #6)
Extension for Firefox/Thunderbird: Master Password+    Back/Forward History Tweaks (restartless)    Cookies Manager+    Fit Images (restartless for Thunderbird)

Offline relu

  • Newbie
  • *
  • Posts: 44
    • View Profile
    • Pulsarmedia
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #3 on: January 15, 2009, 05:43:15 PM »
I will check it and i will tell you if is better. Do you think the original script without any mood installed must be ok? I have around 30.000 wallpapers and the site is going well but when i have more visitors... everything is different and i get that server load warnings. It was the time when 4000 visitors every day but almost every day something is goin on. I like the script and for me is a complete one and i develop my template in more than 6 month every day working something. I really want to make it to work right. I can give you my admin detail if helps more to figure what the problem is. I'm really lost in so much coding sometime.

www.pulsarmedia.eu

Offline relu

  • Newbie
  • *
  • Posts: 44
    • View Profile
    • Pulsarmedia
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #4 on: January 17, 2009, 07:09:37 PM »
I put your code and also i changed in funtions.php getimagesize with filesize (for who use // MOD multi download // getimagesize must be unchanged in that section)  and now everything is really ok :)  Load is normal and google crawl rate is set to full :)  A few minutes ago i had 190 guests including boots and load was 2.8 :) Verry much thanks !!

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Global Moderator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #5 on: January 17, 2009, 08:26:57 PM »
I'm glad (and must say little surprised) it helped...

one thing I forgot to mention is that it caches only for 10 seconds, which controls by this line:
Code: [Select]
  if (file_exists($cat_cache_file) && filemtime($cat_cache_file) > (time() - 10) && $cat_cache_data = file($cat_cache_file))
Your first three "must do" before you ask a question:
Please do not PM me asking for help unless you've been specifically asked to do so. Such PMs will be deleted without answer. (forum rule #6)
Extension for Firefox/Thunderbird: Master Password+    Back/Forward History Tweaks (restartless)    Cookies Manager+    Fit Images (restartless for Thunderbird)

Offline relu

  • Newbie
  • *
  • Posts: 44
    • View Profile
    • Pulsarmedia
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #6 on: January 19, 2009, 05:44:56 AM »
I made just this two modifications and is better like before...What value you think i must enter?  Also i want to ask you if you know anything else what i can do in this direction. Now i disabled the cache for categories and for detail page for comment and for sort images to work but i want to prepare for much visitors in future. Thank you!

Offline relu

  • Newbie
  • *
  • Posts: 44
    • View Profile
    • Pulsarmedia
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #7 on: February 13, 2009, 07:53:39 PM »
I notify something...If guest users is browsing the page eveithing is fine but sometime when i have on page 4-5 registered users the server load is high... i cant explained and i don't know where to look to see what is causing this load. I still looking for any solutions to improve this load.

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Global Moderator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #8 on: February 14, 2009, 12:29:40 AM »
maybe it's because registered users have access to more images/categories?
Your first three "must do" before you ask a question:
Please do not PM me asking for help unless you've been specifically asked to do so. Such PMs will be deleted without answer. (forum rule #6)
Extension for Firefox/Thunderbird: Master Password+    Back/Forward History Tweaks (restartless)    Cookies Manager+    Fit Images (restartless for Thunderbird)

Offline relu

  • Newbie
  • *
  • Posts: 44
    • View Profile
    • Pulsarmedia
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #9 on: February 14, 2009, 01:28:25 AM »
I restricted just download option and no more categories will show after registration.  Is any way to chech what can do sometime this load?  On server or in any place?

Offline Joss

  • Jr. Member
  • **
  • Posts: 68
    • View Profile
    • All about digital photography
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #10 on: October 26, 2009, 03:40:11 PM »
Could you explain what is cached by this hack? The category page (if so, what the difference from $cache_page_categories=1)?
Digital Photo Gallery: http://gallery.imagemaster.ru

Offline V@nо

  • Addicted member
  • ******
  • Posts: 1.223
    • View Profile
Re: Caching "SELECT cat_id, COUNT(*)..." from global.php
« Reply #11 on: October 26, 2009, 04:02:33 PM »
this hack affects pretty much all pages. it probably only works on 4images with a lot of categories and slow mysql server, it has nothing to do with bult-in caching system
Your first three "must do" before you ask a question:
If I asked you to PM me, I meant PM to my primary account, this account doesn't accept PMs.