Reducing Queries Help

[urmomma]

Hello, my gallery is here:
http://www.glitchseekers.com/image_gallery/index.php and I'm currently using 47 queries on index. Now the server I'm hosted at is pretty fast but I currently have a 50,000 querie limit per hour and I have gone over that a few times which caused the site downtime.

I just recently started to advertise and I project about 1,000 unique hits per day in about a month and when I did the math, 50,000 queries per hour can be acheived alot more frequently when the index page itself uses 47 queries with more users coming.

Of course I have taken into account that 50,000 isn't that many so I'm talking with my host about upgrading my package to allow more queries but I believe there is no need for CPG to use 47 quereis on Index when my forum only uses 6 queries on the Index.

I have read a few other threads and did what they advised. Putting all my images into sub-categories and removing thumbnails off index which I'm currently working on as you can see. What else can I do to lower that amount of queries? Thanks in advance!

[Nibbler]

Enable debug mode and see/post what the queries actually are.

[urmomma]

Code Select Expand

USER:
------------------
Array
(
    [ID] => 1873c318f8ff676b41fa44aa849f49d4
    [am] => 1
    [lap] => 1
    [liv] => Array
        (
            [0] => 996
            [1] => 493
            [2] => 2008
            [3] => 1134
            [4] => 1208
        )

)

==========================
USER DATA:
------------------
Array
(
    [0] => 2
    [user_id] => 2
    [1] => urmomma
    [user_name] => urmomma
    [2] => 4
    [mgroup] => 4
    [3] => 4
    [group_id] => 4
    [4] => Admin
    [group_name] => Admin
    [5] => 1024
    [group_quota] => 1024
    [6] => 0
    [has_admin_access] => 1
    [7] => 1
    [can_rate_pictures] => 1
    [8] => 0
    [can_send_ecards] => 0
    [9] => 1
    [can_post_comments] => 1
    [10] => 1
    [can_upload_pictures] => 1
    [11] => 0
    [can_create_albums] => 0
    [12] => 0
    [pub_upl_need_approval] => 0
    [13] => 1
    [priv_upl_need_approval] => 1
    [14] => 3
    [upload_form_config] => 3
    [15] => 0
    [custom_user_upload] => 0
    [16] => 10
    [num_file_upload] => 10
    [17] => 10
    [num_URI_upload] => 10
    [groups] => Array
        (
            [0] => 4
        )

    [can_see_all_albums] => 1
)

==========================
Queries:
------------------
Array
(
    [0] => SELECT extension, mime, content FROM cpg133_filetypes;
    [1] => SELECT id as user_id, name as user_name, mgroup FROM `(database name removed for security purposes ;))`.ibf_members WHERE id='2' AND  member_login_key='61f860d43d70b2be3e276dfe7666f1a5'
    [2] => SELECT * FROM cpg133_usergroups WHERE group_id = '4'
    [3] => DELETE FROM cpg133_banned WHERE expiry < '2005-12-13 12:45:09'
    [4] => SELECT * FROM cpg133_banned WHERE ip_addr='71.*.*.*(removed ip)' OR ip_addr='71.*.*.*(removed ip)' OR user_id=2
    [5] => SELECT cid, name, description, thumb FROM cpg133_categories WHERE parent = ''  ORDER BY pos
    [6] => SELECT aid FROM cpg133_albums WHERE category = 2
    [7] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 2
    [8] => SELECT count(*) FROM cpg133_albums as a WHERE category = '2'
    [9] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg133_albums as a LEFT JOIN cpg133_pictures as p ON a.thumb=p.pid WHERE category=2 ORDER BY a.pos LIMIT 0,1
    [10] => SELECT aid, count(pid) as pic_count, max(pid) as last_pid, max(ctime) as last_upload FROM cpg133_pictures WHERE aid IN (1) AND approved = 'YES' GROUP BY aid
    [11] => SELECT cid, name, description, thumb FROM cpg133_categories WHERE parent = '2'  ORDER BY pos
    [12] => SELECT aid FROM cpg133_albums WHERE category = 7
    [13] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 7
    [14] => SELECT aid FROM cpg133_albums WHERE category = 8
    [15] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 8
    [16] => SELECT aid FROM cpg133_albums WHERE category = 9
    [17] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 9
    [18] => SELECT aid FROM cpg133_albums WHERE category = 3
    [19] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 3
    [20] => SELECT cid, name, description, thumb FROM cpg133_categories WHERE parent = '3'  ORDER BY pos
    [21] => SELECT aid FROM cpg133_albums as a WHERE category>=10000
    [22] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category >= 10000
    [23] => SELECT aid FROM cpg133_albums WHERE category = 5
    [24] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 5
    [25] => SELECT aid FROM cpg133_albums WHERE category = 11
    [26] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 11
    [27] => SELECT aid FROM cpg133_albums WHERE category = 12
    [28] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 12
    [29] => SELECT cid, name, description, thumb FROM cpg133_categories WHERE parent = '12'  ORDER BY pos
    [30] => SELECT aid FROM cpg133_albums WHERE category = 10
    [31] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 10
    [32] => SELECT aid FROM cpg133_albums WHERE category = 13
    [33] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 13
    [34] => SELECT aid FROM cpg133_albums WHERE category = 14
    [35] => SELECT count(*) FROM cpg133_pictures as p, cpg133_albums as a WHERE p.aid = a.aid AND category = 14
    [36] => SELECT aid FROM cpg133_albums as a WHERE category = '0'
    [37] => SELECT count(*) FROM cpg133_albums as a WHERE 1
    [38] => SELECT count(*) FROM cpg133_pictures as p LEFT JOIN cpg133_albums as a ON a.aid=p.aid WHERE 1
    [39] => SELECT count(*) FROM cpg133_comments as c LEFT JOIN cpg133_pictures as p ON c.pid=p.pid LEFT JOIN cpg133_albums as a ON a.aid=p.aid WHERE 1
    [40] => SELECT count(*) FROM cpg133_categories WHERE 1
    [41] => SELECT sum(hits) FROM cpg133_pictures as p LEFT JOIN cpg133_albums as a ON p.aid=a.aid WHERE 1
    [42] => SELECT count(*) FROM cpg133_albums as a WHERE category = '0'
    [43] => SELECT COUNT(*) from cpg133_pictures WHERE approved = 'YES'
    [44] => SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, aid FROM cpg133_pictures WHERE approved = 'YES'  ORDER BY RAND() LIMIT 6
    [45] => SELECT COUNT(*) from cpg133_pictures WHERE approved = 'YES'
    [46] => SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid,title, caption, owner_id, owner_name, aid FROM cpg133_pictures WHERE approved = 'YES'  ORDER BY pid DESC  LIMIT 0 ,6
)

==========================
GET :
------------------
Array
(
)

==========================
POST :
------------------
Array
(
)

==========================
VERSION INFO :
------------------
PHP version: 4.3.10 - OK
------------------
mySQL version: 4.0.16
------------------
Coppermine version: 1.3.3
==========================
Module: gd
------------------
GD Support enabled
GD Version bundled (2.0.28 compatible)
FreeType Support enabled
FreeType Linkage with freetype
GIF Read Support enabled
GIF Create Support enabled
JPG Support enabled
PNG Support enabled
WBMP Support enabled
XBM Support enabled
==========================
Module: mysql
------------------
Active Persistent Links 0
Active Links 1
Client API version 3.23.49
MYSQL_MODULE_TYPE builtin
MYSQL_SOCKET /tmp/mysql.sock
MYSQL_INCLUDE no value
MYSQL_LIBS no value
==========================
Module: zlib
------------------
ZLib Support enabled
Compiled Version 1.2.1
Linked Version 1.2.1
==========================
Server restrictions (safe mode)?
------------------
Directive | Local Value | Master Value
safe_mode | Off | Off
safe_mode_exec_dir | no value | no value
safe_mode_gid | Off | Off
safe_mode_include_dir | no value | no value
safe_mode_exec_dir | no value | no value
sql.safe_mode | Off | Off
disable_functions | no value | no value
file_uploads | On | On
include_path | .:/usr/local/lib/php | .:/usr/local/lib/php
open_basedir | no value | no value
==========================
email
------------------
Directive | Local Value | Master Value
sendmail_from | me@localhost.com | me@localhost.com
sendmail_path | /usr/sbin/sendmail -i -t | /usr/sbin/sendmail -i -t
SMTP | localhost | localhost
smtp_port | 25 | 25
==========================
Size and Time
------------------
Directive | Local Value | Master Value
max_execution_time | 30 | 30
max_input_time | -1 | -1
upload_max_filesize | 15M | 15M
post_max_size | 8M | 8M
==========================
Page generated in 0.292 seconds - 47 queries in 0.231 seconds - Album set :
That is the index. I am bridged with my IPB 2.0.0 forum. Thanks for helping Nibbler! I really appreciate it. I'm going to search this board...for a mod to change some queries into sessions now...

[Nibbler]

If you don't use banning you can remove that code from include/init.inc.php

Code Select Expand


// Remove expired bans
$now = date('Y-m-d H:i:s');
db_query("DELETE FROM {$CONFIG['TABLE_BANNED']} WHERE expiry < '$now'");
// Check if the user is banned
$user_id = USER_ID;
$result = db_query("SELECT * FROM {$CONFIG['TABLE_BANNED']} WHERE ip_addr='$raw_ip' OR ip_addr='$hdr_ip' OR user_id=$user_id");
if (mysql_num_rows($result)) {
    pageheader($lang_error);
    msg_box($lang_info, $lang_errors['banned']);
    pagefooter();
    exit;
}


You can remove the gallery stats (4331 files in 178 albums and 11 categories with 59 comments viewed 15039 times) by changing this:

Code Select Expand


    // Gather gallery statistics
    if ($cat == 0) {
        $result = db_query("SELECT count(*) FROM {$CONFIG['TABLE_ALBUMS']} as a WHERE 1".$album_filter);
        $nbEnr = mysql_fetch_array($result);
        $album_count = $nbEnr[0];
        mysql_free_result($result);

        $sql = "SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']} as p ".
                'LEFT JOIN '.$CONFIG['TABLE_ALBUMS'].' as a '.
                'ON a.aid=p.aid '.
                'WHERE 1'.$pic_filter;
        $result = db_query($sql);
        $nbEnr = mysql_fetch_array($result);
        $picture_count = $nbEnr[0];
        mysql_free_result($result);

        $sql = "SELECT count(*) FROM {$CONFIG['TABLE_COMMENTS']} as c ".
                'LEFT JOIN '.$CONFIG['TABLE_PICTURES'].' as p '.
                'ON c.pid=p.pid '.
                'LEFT JOIN '.$CONFIG['TABLE_ALBUMS'].' as a '.
                'ON a.aid=p.aid '.
                'WHERE 1'.$pic_filter;
        $result = db_query($sql);
        $nbEnr = mysql_fetch_array($result);
        $comment_count = $nbEnr[0];
        mysql_free_result($result);

        $sql = "SELECT count(*) FROM {$CONFIG['TABLE_CATEGORIES']} WHERE 1";
        $result = db_query($sql);
        $nbEnr = mysql_fetch_array($result);
        $cat_count = $nbEnr[0] - $HIDE_USER_CAT;
        mysql_free_result($result);

        $sql = "SELECT sum(hits) FROM {$CONFIG['TABLE_PICTURES']} as p ".
                'LEFT JOIN '.$CONFIG['TABLE_ALBUMS'].' as a '.
                'ON p.aid=a.aid '.
                'WHERE 1'.$pic_filter;
        $result = db_query($sql);
        $nbEnr = mysql_fetch_array($result);
        $hit_count = (int)$nbEnr[0];
        mysql_free_result($result);

        if (count($cat_data)) {
            $statistics = strtr($lang_list_categories['stat1'], array('[pictures]' => $picture_count,
                    '[albums]' => $album_count,
                    '[cat]' => $cat_count,
                    '[comments]' => $comment_count,
                    '[views]' => $hit_count));
        } else {
            $STATS_IN_ALB_LIST = true;
            $statistics = strtr($lang_list_categories['stat3'], array('[pictures]' => $picture_count,
                    '[albums]' => $album_count,
                    '[comments]' => $comment_count,
                    '[views]' => $hit_count));
        }
    } elseif ($cat >= FIRST_USER_CAT && $ALBUM_SET) {
        $result = db_query("SELECT count(*) FROM {$CONFIG['TABLE_ALBUMS']} WHERE 1 $current_album_set");
        $nbEnr = mysql_fetch_array($result);
        $album_count = $nbEnr[0];
        mysql_free_result($result);

        $result = db_query("SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']} WHERE 1 $current_album_set");
        $nbEnr = mysql_fetch_array($result);
        $picture_count = $nbEnr[0];
        mysql_free_result($result);

        $result = db_query("SELECT sum(hits) FROM {$CONFIG['TABLE_PICTURES']} WHERE 1 $current_album_set");
        $nbEnr = mysql_fetch_array($result);
        $hit_count = (int)$nbEnr[0];
        mysql_free_result($result);

        $statistics = strtr($lang_list_categories['stat2'], array('[pictures]' => $picture_count,
                '[albums]' => $album_count,
                '[views]' => $hit_count));
    } else {
        $statistics = '';
    }


to

Code Select Expand


$statistics = '';


in your index.php

You could also remove the meta albums from the main page (config setting).

[urmomma]

thanks so much Nibbler for the super fast response! I will do those changes.

You suggest:

You could also remove the meta albums from the main page (config setting).

I'm curious what will that do? Does that remove meta tags so in the end it might be harder for bots to index the Image Gallery?

Well, I will try it all out and see what happens! Also, a roomate of mine, during the Winter Holidays (starting next week) are going to go through the code (read the whole thing) and try to make a "mod" for users to change alot of these queries into sessions. At least these global vars that run 6 queries per hit... Well, thanks again!

[Nibbler]

meta albums are the lastup and random 'albums' on the main page.

[urmomma]

When I remove the "ban" code I get the follow error in a blank white page:

Code Select Expand

Parse error: parse error, unexpected T_IF in /home/glitchse/public_html/image_gallery/include/init.inc.php on line 59
Does it have something to do with be bridged to IPB 2.0.0?

Also, when I remove that large chunk of code to remove statistics I get the following error in a blank white page (as you can see now...which for some reason isn't going away even if I put back the original code...):

Code Select Expand

Parse error: parse error, unexpected ')' in /home/glitchse/public_html/image_gallery/index.php on line 226

EDIT: nvm...when I uploaded it through FTP, seems to be working again. But still I would get those errors when I did the code changes. I am going to do the code changes on my computer then upload them and see if that does the trick.

[urmomma]

any other ways to remove the amount of queries?

Thanks for helping out!