how to reduce loading time of index.php

[Bradster]

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.

[Αndré]

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

[Joe Carver]

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

In index.php here:

Code Select Expand

       // Inserts a thumbnail if the album contains 1 or more images

Thumbnail queries are run under this condition:

Code Select Expand

                // Inserts a thumbnail if the album contains 1 or more images

Even when the thumbnails are not shown (this is not checked):

Code Select Expand

    $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é]

Thanks. I'll have a closer look soon.

[Αndré]

We don't need to run list_cat_albums if $CONFIG['first_level'] is false. We should replace

Code Select Expand

                // Check if you need to show first level album thumbnails
                if ($level <= $CONFIG['subcat_level']) {
with

Code Select Expand

                // 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.

[Joe Carver]

Thanks - I suspected something better could be done.

[Bradster]

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. 

[Αndré]

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?

[Bradster]

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.

[Αndré]

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).

[Bradster]

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?

[Αndré]

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.

[Αndré]

Open index.php, find

Code Select Expand

    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 Expand

    $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 Expand

                    $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 Expand

                    $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.

[Αndré]

Open index.php, find

Code Select Expand

    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 Expand

    $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 Expand

                // 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 Expand

                if (!empty($last_pid_data[$album['thumb']]['filename'])) {
                    $picture = $last_pid_data[$album['thumb']];

find

Code Select Expand

                    $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 Expand

                    $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.

[Bradster]

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

[Αndré]

Sorry, I forgot to copy 1 line. Please add

Code Select Expand

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

Code Select Expand

    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.

[Bradster]

Error on  line 1164

    }

[Αndré]

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.

[Niecher]

Hello André,

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.

[Αndré]

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.