Advanced search  

News:

cpg1.5.46 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter recently discovered vulnerabilities. It is important that all users who run version cpg1.5.44 or older update to this latest version as soon as possible.
[more]

Pages: 1 [2] 3   Go Down

Author Topic: how to reduce loading time of index.php  (Read 26598 times)

0 Members and 1 Guest are viewing this topic.

Bradster

  • Coppermine novice
  • *
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 21
Re: how to reduce loading time of index.php
« Reply #20 on: July 30, 2013, 10:55:53 pm »

I'll be away from my PC for a week and my phone won't be the best test platform for the DOE I have in mind based on your request, so I'll have to put this off for a bit. I'll test 6 categories (a pair from 3 size ranges) with the old and new index.php file, and note the number of queries and generation time. Might also take the average of several refreshes, because I occasionally saw some noticeably different times and should figure out if they're outliers or not.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #21 on: August 23, 2013, 03:16:35 pm »

Joe: I just glanced through the thread, is there anything to fix in the Coppermine code or is this just an individual issue?
Logged

Joe Carver

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1498
  • aka 'i-imagine'
    • Home Page
Re: how to reduce loading time of index.php
« Reply #22 on: August 30, 2013, 02:01:58 am »

Αndré, there might be a need for a change or another tester.  :)  Here is what I have observed:

In index.php here:
Code: [Select]
       // Inserts a thumbnail if the album contains 1 or more images
Thumbnail queries are run under this condition:
Code: [Select]
                // Inserts a thumbnail if the album contains 1 or more images
Even when the thumbnails are not shown (this is not checked):
Code: [Select]
    $CONFIG['first_level']
The apparent result is a larger number of sql queries than the page needs.

The original poster has reported no negative effects (yet) and in my limited testing gallery there was a decrease in the amount queries.
Since I am still not certain if adding the first_level check will break something else, I hope this can help to answer your question.

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #23 on: August 30, 2013, 09:02:13 am »

Thanks. I'll have a closer look soon.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #24 on: August 30, 2013, 11:45:41 am »

We don't need to run list_cat_albums if $CONFIG['first_level'] is false. We should replace
Code: [Select]
                // Check if you need to show first level album thumbnails
                if ($level <= $CONFIG['subcat_level']) {
with
Code: [Select]
                // Check if you need to show first level album thumbnails
                if ($CONFIG['first_level'] && $level <= $CONFIG['subcat_level']) {
as it's the more logical and performant solution IMHO.
Logged

Joe Carver

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1498
  • aka 'i-imagine'
    • Home Page
Re: how to reduce loading time of index.php
« Reply #25 on: August 30, 2013, 07:00:24 pm »

Thanks - I suspected something better could be done.

Bradster

  • Coppermine novice
  • *
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 21
Re: how to reduce loading time of index.php
« Reply #26 on: September 02, 2013, 10:38:27 pm »

Sorry for not getting back to this thread after returning home and running a few tests. Though it looks like there has been another change to index.php proposed (and possibly rendering all of this moot), here's the outcome of the brief fiddling I did before my time was allocated elsewhere:

https://www.dropbox.com/s/4egymvjevac0j4i/afg%20page%20testing.xlsx

Values are the average of 3 page refresh.

Thanks again for all the help- I'm not sure how many others have sites with an image count that's this size, but I'm sure they'll benefit from this as well.  :)
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #27 on: September 03, 2013, 09:28:28 am »

I don't understand that table, at least I don't understand how it's related to your gallery. As far as I can see you don't display any category thumbs in your gallery. Where did you get the "number of category thumbs" in this case?
Logged

Bradster

  • Coppermine novice
  • *
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 21
Re: how to reduce loading time of index.php
« Reply #28 on: September 03, 2013, 04:51:55 pm »

Sorry, that table was made after I implemented Joe's modification of index.php. I noticed that that page loaded faster, but the category pages loaded slower, and he was curious to know which ones and how much slower.

Can you post link to any particular page(s) with that behavior? And do you know if the
difference is due to increased query count? Or could you take a look at the count and time
differences? Again, my test setup is not as large by any means, so any large scale gallery effects
are impossible for me to test.

I then changed the number of thumbs per category page, going in increments of 10 for S/M/L-sized galleries to see what the time and query differences were for each setting.

I kinda stopped after doing 10 entries of the new index.php 'cos each one is an average of 2-3 galleries, and I didn't dare try this test with the native file out of fear of getting the stink eye from my host.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #29 on: September 04, 2013, 09:26:41 am »

I assume we're talking about pages like this one? http://anime-fanservice.org/coppermine/index.php?cat=30

Actually, those are album thumbnails, not category thumbnails. If you confirm, I'll check if we can optimize the corresponding code  (decrease query amount).
Logged

Bradster

  • Coppermine novice
  • *
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 21
Re: how to reduce loading time of index.php
« Reply #30 on: September 05, 2013, 05:02:04 pm »

Sorry, yes, they're album thumbnails (from "number of albums to display" under "Album List View" in Config). The smaller this number was, the (relatively) quicker the main page would load under the normal index.php- but I didn't want categories that have over 50 albums to be split into such tiny chunks.

Do you want me to give your code replacement suggestion from 8/30 a try, or is that a simple change that has no chance of affecting other code/config settings?
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #31 on: September 05, 2013, 05:08:54 pm »

That code change just affects the category thumbnails. It has no effect on the album thumbnail pages. I'll check if we can improve your issue probably tomorrow.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #32 on: September 06, 2013, 10:45:03 am »

Open index.php, find
Code: [Select]
    foreach ($alb_stats as $key => $value) {
        $cross_ref[$value['aid']] = &$alb_stats[$key];
        if ($CONFIG['link_pic_count'] == 1 || $value['pic_count'] == 0) {
            if (!empty($value['keyword'])) {
                $keyword = ($value['keyword'] ? "AND (keywords like '%".addslashes($value['keyword'])."%' $forbidden_set_string )" : '');
                $query = "SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid "
                        ." FROM {$CONFIG['TABLE_PICTURES']} "
                        ." WHERE ((aid != '{$value['aid']}' $forbidden_set_string) $keyword) $approved";
                $result = cpg_db_query($query);
                $link_stat = mysql_fetch_assoc($result);
                mysql_free_result($result);
                $alb_stats[$key]['link_pic_count'] = $link_stat['link_pic_count'];
                $alb_stats[$key]['last_pid'] = ($alb_stats[$key]['last_pid'] > $link_stat['link_last_pid']) ? $alb_stats[$key]['last_pid'] : $link_stat['link_last_pid'];
            }
        }
    }
and replace with
Code: [Select]
    $last_pids = array();
    $last_pid_data = array();

    foreach ($alb_stats as $key => $value) {
        $cross_ref[$value['aid']] = &$alb_stats[$key];
        if ($CONFIG['link_pic_count'] == 1 || $value['pic_count'] == 0) {
            if (!empty($value['keyword'])) {
                $keyword = ($value['keyword'] ? "AND (keywords like '%".addslashes($value['keyword'])."%' $forbidden_set_string )" : '');
                $query = "SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid "
                        ." FROM {$CONFIG['TABLE_PICTURES']} "
                        ." WHERE ((aid != '{$value['aid']}' $forbidden_set_string) $keyword) $approved";
                $result = cpg_db_query($query);
                $link_stat = mysql_fetch_assoc($result);
                mysql_free_result($result);
                $alb_stats[$key]['link_pic_count'] = $link_stat['link_pic_count'];
                $alb_stats[$key]['last_pid'] = ($alb_stats[$key]['last_pid'] > $link_stat['link_last_pid']) ? $alb_stats[$key]['last_pid'] : $link_stat['link_last_pid'];
            }
        }
        if ($alb_stats[$key]['last_pid']) {
            $last_pids[] = $alb_stats[$key]['last_pid'];
        }
    }

    if (count($last_pids)) {
        $result = cpg_db_query("SELECT pid, filepath, filename, url_prefix, pwidth, pheight FROM {$CONFIG['TABLE_PICTURES']} WHERE pid IN (".implode(',', $last_pids).")");
        while ($row = mysql_fetch_assoc($result)) {
            $last_pid_data[$row['pid']] = $row;
            unset($last_pid_data[$row['pid']]['pid']);
        }
        mysql_free_result($result);
    }
    unset($last_pids);

find
Code: [Select]
                    $sql = "SELECT filepath, filename, url_prefix, pwidth, pheight "
                        . "FROM {$CONFIG['TABLE_PICTURES']} WHERE pid='{$alb_stat['last_pid']}'";
                    $result = cpg_db_query($sql);
                    $picture = mysql_fetch_assoc($result);
                    mysql_free_result($result);
and replace with
Code: [Select]
                    $picture = $last_pid_data[$alb_stat['last_pid']];

This should reduce the query amount if you use "last uploaded" as album thumbnail. We still need to optimize it for albums that will be displayed through the function list_cat_albums, I'll post it as soon as possible.
« Last Edit: September 13, 2013, 12:07:57 pm by Αndré »
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #33 on: September 06, 2013, 11:14:25 am »

Open index.php, find
Code: [Select]
    foreach ($catdata['subalbums'] as $aid => $album) {

        $approved = ' AND approved=\'YES\'';
        $forbidden_set_string = ((count($FORBIDDEN_SET_DATA) > 0) ? ' AND aid NOT IN (' . implode(', ', $FORBIDDEN_SET_DATA) . ')' : '');
        $keyword = ($album['keyword'] ? "AND (keywords like '%".addslashes($album['keyword'])."%' $forbidden_set_string)" : '');
        if ($CONFIG['link_pic_count'] == 1 || $album['pic_count'] == 0) {

            if (!empty($album['keyword'])) {
                $query = "SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid "
                        ." FROM {$CONFIG['TABLE_PICTURES']} "
                        ." WHERE ((aid != '$aid' $forbidden_set_string) $keyword) $approved";
                $result = cpg_db_query($query);
                $link_stat = mysql_fetch_assoc($result);
                mysql_free_result($result);
                $album['link_pic_count'] = $link_stat['link_pic_count'];
                $album['last_pid'] = !empty($album['last_pid']) && ($album['last_pid'] > $link_stat['link_last_pid']) ? $album['last_pid'] : $link_stat['link_last_pid'];
            }
        }
and replace with
Code: [Select]
    $approved = ' AND approved=\'YES\'';
    $forbidden_set_string = ((count($FORBIDDEN_SET_DATA) > 0) ? ' AND aid NOT IN (' . implode(', ', $FORBIDDEN_SET_DATA) . ')' : '');

    $last_pids = array();
    $last_pid_data = array();

    foreach ($catdata['subalbums'] as $aid => $album) {
        if ($CONFIG['link_pic_count'] == 1 || $album['pic_count'] == 0) {
            if (!empty($album['keyword'])) {
                $keyword = ($album['keyword'] ? "AND (keywords like '%".addslashes($album['keyword'])."%' $forbidden_set_string)" : '');
                $query = "SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid "
                        ." FROM {$CONFIG['TABLE_PICTURES']} "
                        ." WHERE ((aid != '$aid' $forbidden_set_string) $keyword) $approved";
                $result = cpg_db_query($query);
                $link_stat = mysql_fetch_assoc($result);
                mysql_free_result($result);
                $catdata['subalbums'][$aid]['link_pic_count'] = $link_stat['link_pic_count'];
                $catdata['subalbums'][$aid]['last_pid'] = !empty($album['last_pid']) && ($album['last_pid'] > $link_stat['link_last_pid']) ? $album['last_pid'] : $link_stat['link_last_pid'];
            }
        }
        if ($catdata['subalbums'][$aid]['last_pid']) {
            $last_pids[] = $catdata['subalbums'][$aid]['last_pid'];
        }
        if ($album['thumb'] > 0) {
            $last_pids[] = $album['thumb'];
        }
    }

    if (count($last_pids)) {
        $result = cpg_db_query("SELECT pid, filepath, filename, url_prefix, pwidth, pheight FROM {$CONFIG['TABLE_PICTURES']} WHERE pid IN (".implode(',', $last_pids).")");
        while ($row = mysql_fetch_assoc($result)) {
            $last_pid_data[$row['pid']] = $row;
            unset($last_pid_data[$row['pid']]['pid']);
        }
        mysql_free_result($result);
    }
    unset($last_pids);

    foreach ($catdata['subalbums'] as $aid => $album) {

find
Code: [Select]
                // Inserts a thumbnail if the album contains 1 or more images
                if ($album['thumb'] > 0) {
                    $sql = "SELECT filepath, filename, url_prefix, pwidth, pheight "
                            ." FROM {$CONFIG['TABLE_PICTURES']} WHERE pid='{$album['thumb']}'";
                    $result = cpg_db_query($sql);
                    $picture = mysql_fetch_assoc($result);
                    if (!is_array($picture)) {
                        $sql = "SELECT filepath, filename, url_prefix, pwidth, pheight "
                            . "FROM {$CONFIG['TABLE_PICTURES']} WHERE pid='{$album['last_pid']}'";
                        $result = cpg_db_query($sql);
                        $picture = mysql_fetch_assoc($result);
                    }
                    mysql_free_result($result);
and replace with
Code: [Select]
                if (!empty($last_pid_data[$album['thumb']]['filename'])) {
                    $picture = $last_pid_data[$album['thumb']];

find
Code: [Select]
                    $sql = "SELECT filepath, filename, url_prefix, pwidth, pheight "
                        . "FROM {$CONFIG['TABLE_PICTURES']} WHERE pid='{$album['last_pid']}'";
                    $result = cpg_db_query($sql);
                    $picture = mysql_fetch_assoc($result);
                    mysql_free_result($result);
and replace with
Code: [Select]
                    $picture = $last_pid_data[$album['last_pid']];

As you maybe noticed I also optimized the query amount if you set a particular picture as album thumbnail (that's why we need to replace 3 blocks instead of 2 blocks).


Please report if everything works as expected. To test all changes, please enable "Show first level album thumbnails in categories" in the config, if not already done. Have a look at the query count and if any selected album thumbnail remains. Thank you.

If everything works as expected, I'll commit the changes to our SVN repository.
« Last Edit: September 13, 2013, 12:01:09 pm by Αndré »
Logged

Bradster

  • Coppermine novice
  • *
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 21
Re: how to reduce loading time of index.php
« Reply #34 on: September 12, 2013, 05:44:12 am »

Sorry for the delay. Browser message is:

Parse error: syntax error, unexpected '}' in /home/content/27/3728527/html/coppermine/index.php on line 1143


line 1143 appears to be } // function list_cat_albums
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #35 on: September 12, 2013, 10:20:58 am »

Sorry, I forgot to copy 1 line. Please add
Code: [Select]
foreach ($catdata['subalbums'] as $aid => $album) {after
Code: [Select]
    foreach ($catdata['subalbums'] as $aid => $album) {
        if ($CONFIG['link_pic_count'] == 1 || $album['pic_count'] == 0) {
            if (!empty($album['keyword'])) {
                $keyword = ($album['keyword'] ? "AND (keywords like '%".addslashes($album['keyword'])."%' $forbidden_set_string)" : '');
                $query = "SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid "
                        ." FROM {$CONFIG['TABLE_PICTURES']} "
                        ." WHERE ((aid != '$aid' $forbidden_set_string) $keyword) $approved";
                $result = cpg_db_query($query);
                $link_stat = mysql_fetch_assoc($result);
                mysql_free_result($result);
                $catdata['subalbums'][$aid]['link_pic_count'] = $link_stat['link_pic_count'];
                $catdata['subalbums'][$aid]['last_pid'] = !empty($album['last_pid']) && ($album['last_pid'] > $link_stat['link_last_pid']) ? $album['last_pid'] : $link_stat['link_last_pid'];
            }
        }
        $last_pids[] = $catdata['subalbums'][$aid]['last_pid'];
        if ($album['thumb'] > 0) {
            $last_pids[] = $album['thumb'];
        }
    }

    if (count($last_pids)) {
        $result = cpg_db_query("SELECT pid, filepath, filename, url_prefix, pwidth, pheight FROM {$CONFIG['TABLE_PICTURES']} WHERE pid IN (".implode(',', $last_pids).")");
        while ($row = mysql_fetch_assoc($result)) {
            $last_pid_data[$row['pid']] = $row;
            unset($last_pid_data[$row['pid']]['pid']);
        }
        mysql_free_result($result);
    }
    unset($last_pids);

I'll also update my above instructions accordingly.
Logged

Bradster

  • Coppermine novice
  • *
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 21
Re: how to reduce loading time of index.php
« Reply #36 on: September 13, 2013, 05:01:58 am »

Error on  line 1164

    }
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #37 on: September 13, 2013, 12:11:05 pm »

I just tested the above code changes at a different testbed and don't get any parse errors. However, I got an MySQL issue which I just fixed in the above code.

I suggest to undo the changes from my 2 posts at 2013-09-06 and re-apply them.
Logged

Niecher

  • LocalSupporter
  • Coppermine frequent poster
  • ***
  • Country: es
  • Offline Offline
  • Gender: Male
  • Posts: 191
Re: how to reduce loading time of index.php
« Reply #38 on: September 13, 2013, 01:14:47 pm »

Hello André,

Quote
I have tested the code before you edited your post today.

The code worked perfectly until I've removed a duplicate file of the gallery, and then, when loading the index.php page displays a critical error in the database.

Now when I put the code after you edit your post today, index.php page is blank.

---Edit---

I think the code I had pasted wrongly,  :-[

now works perfectly.

Certainly has greatly improved the speed of loading index.php

Regards.
« Last Edit: September 13, 2013, 02:05:51 pm by Niecher »
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: how to reduce loading time of index.php
« Reply #39 on: September 13, 2013, 02:08:56 pm »

Thanks for confirmation. If Bradster (and maybe also some other people) confirm that the fix works as expected, I'll commit it to our SVN repository and it will be part of the next cpg1.5.x release.
Logged
Pages: 1 [2] 3   Go Up
 

Page created in 0.024 seconds with 20 queries.