gallery problem

[amylee]

would be grateful if anybody could help me with an issue i'm having atm with my gallery trying to update my script to incorperate a sitebuilder but this is really bugging me and slowing me down

only problem is the amount of time it takes to open like for example i go from my index page and click on the gallery link it takes around 15-25 seconds to open
yet once in the gallery pictures open in less than a second then when returning to gallery takes ages to go back


i'm stuck on where to fix this, got a feeling it lies with the sql and grabbing the info from the database but i have re-written it many times problem is still there and it's the only issue i'm having that i can't seem to fix :/

here is my whole gallery file

<?php

if(!defined('IN_RAVEWC'))
{
    $fp = fopen("../log/hack.log","a");
    $ip=getenv('REMOTE_ADDR');
    $dt = date("d-m-Y (H:i)");
    $info = $ip.": ".$dt." :File-".$_SERVER['SCRIPT_NAME']."\n";
    fwrite($fp, $info);
    fclose($fp);
    die("**** OFF!");
    exit;
}
$lngfile = getlang($uid);
include_once('lng/'.$lngfile);
$mime =  getmime($uid);
$charset = "utf-8";
header("content-type:$mime;charset=$charset");
header("Cache-Control: no-store, no-cache, must-revalidate");
$pg = $_GET['pg']+0;
$xhtml = false;
if($mime==MIME_XHTML||$mime==MIME_HTML)
{
        $xhtml = true;
}
$csql = "SELECT COUNT(id) FROM gallery WHERE spublic='1' ";
$whr = "WHERE a.spublic='1' ";
$ord = "a.adate DESC";
$id = $_GET['id']+0;
$sx = $_GET['sx'];
$od = $_GET['od']+0;
if($id>0)
{
    $csql .= " AND uid='".$id."'";
    $whr .= " AND a.uid='".$id."'";
}else{
    if($sx=="m"||$sx=="f")
    {
        $csql = "SELECT COUNT(a.id) FROM gallery a INNER JOIN users b ON a.uid=b.id WHERE a.spublic='1' AND b.sex='".strtoupper($sx)."'";
        $whr .= " AND b.sex='".strtoupper($sx)."'";
    }else{
        $whr.="";
    }
}
if($od==3)
{
    $ord = "b.name";
}else if($od==1)
{
    //rate
    $ord = "ir DESC";
}else if($od==2)
{
    //comments
    $ord = "nc DESC";
}else{
    $ord = "a.adate DESC";
}
$skip = true;

echo $xhtml?xhtml_header($lang['gallery'], get_theme($uid)):wml_header($lang['gallery']);

if($xhtml)
{
    echo '<h3 align="center">'.$lang['gallery'].'</h3>';
}
echo '<p align="center">';
if(!$xhtml)
{
    echo $lang['gallery'].'<br/>';
}
echo '<small><a href="index.php?ac=gop&amp;sid='.$sid.'">'.$lang['options'].'</a></small>';
echo '</p>';
echo '<p><small>';

if($skip)
{
    $noi = mysql_fetch_array(mysql_query($csql));
    $num_items = $noi[0];
    $items_per_page = 3;
    $num_pages = ceil($num_items / $items_per_page);
    if($pg==""||$pg==0)$pg=1;
    $limit_start = ($pg-1)*$items_per_page;
    if($num_items>0)
    {
     
    
        $offsets = $utzon*60*60;
        $items = mysql_query("SELECT a.uid, a.url, a.adate, a.viewed, b.name, b.perms, a.isize, a.id, AVG(c.irate) AS ir, COUNT(d.id) AS nc, a.description, a.type FROM ((gallery a LEFT JOIN users b ON a.uid = b.id) LEFT JOIN girate c ON a.id=c.gid) LEFT JOIN gicomments d ON a.id=d.gid ".$whr." GROUP BY a.id  ORDER BY ".$ord." LIMIT $limit_start, $items_per_page");
        while($item = mysql_fetch_array($items))
        {
            echo '<img src="pout/mkimg.php?bi='.($item[11]==0?$item[1]:$item[1].'.jpg').'" alt="*" />'.htmlspecialchars($item[10]).'<br/>
            <a href="index.php?ac=gid&amp;sid='.$sid.'&amp;id='.$item[7].'">'.($item[11]==0?$lang['picture']:$lang['video']).'</a><br/>
            '.$lang['member'].': '.stylize($item[4], $item[5], $xhtml, $sid, $item[0]).'<br/>
            '.$lang['created'].': '.date("d/m/Y-H:i" , $item[2] + $offsets).'<br/>
            '.$lang['views'].': '.$item[3].'<br/>
            '.$lang['rate'].': '.($item[8]+0).'<br/>
            '.$lang['size'].': '.ceil($item[6]/1024).' KB<br/>
            <br/>----------<br/>
            ';
            
            }
    
        
    }else{
        echo $lang['no_items'];
    }
    
}else{
    echo "The gallery functions can't work now because of limitations on the server, this will be fixed soon<br/>";
}
echo '</small></p>';
$uinb = mysql_fetch_array(mysql_query
    (
    "SELECT COUNT(id)
    FROM inbox
    WHERE tid='".$uid."' AND msg_read = '0'
    ;"
    ));
    if($uinb[0]>0)
    {
        $tinb = mysql_fetch_array(mysql_query
        (
            "SELECT COUNT(id)
            FROM inbox
            WHERE tid='".$uid."'
            ;"
        ));
    }
echo '<p align="center">';
if($num_pages>1)
        {
            echo '<small>';
            if($pg>1)
            {
              $ppage = $pg-1;
              echo "<a href=\"?ac=$ac&amp;pg=$ppage&amp;sid=$sid&amp;id=$id&amp;sx=$sx&amp;od=$od\">«".$lang['prev']."</a> ";
            }
            
            echo $pg.'/'."<a href=\"?ac=$ac&amp;pg=$num_pages&amp;sid=$sid&amp;id=$id&amp;sx=$sx&amp;od=$od\">$num_pages</a>";
            if($pg<$num_pages)
            {
              $npage = $pg+1;
              echo " <a href=\"?ac=$ac&amp;pg=$npage&amp;sid=$sid&amp;id=$id&amp;sx=$sx&amp;od=$od\">".$lang['next']."»</a>";
            
            }
            //jumber
            echo '</small><br/><br/>';
        }    
        if($num_pages>2)
        {
            if($xhtml)
            {
                echo '<form action="index.php" method="get">
                <input type="hidden" name="ac" value="'.$ac.'"/>
                <input type="hidden" name="sid" value="'.$sid.'"/>
                <input type="hidden" name="id" value="'.$id.'"/>
                <input type="hidden" name="od" value="'.$od.'"/>
                <input type="hidden" name="sx" value="'.$sx.'"/>
                '.$lang['page'].' <input type="text" name="pg" maxlength="4" size="4" value="'.$pg.'"/>
                <input type="submit" maxlength="4" size="4" value="'.$lang['go'].'"/>
                </form><br/>';
            }else{
                echo $lang['page'].' <input name="pg" maxlength="4" size="4"/>
            <anchor>'.$lang['go'].'<go href="index.php" method="get">
            <postfield name="ac" value="'.$ac.'"/>
            <postfield name="sid" value="'.$sid.'"/>
            <postfield name="id" value="'.$id.'"/>
            <postfield name="od" value="'.$od.'"/>
            <postfield name="sx" value="'.$sx.'"/>
            <postfield name="pg" value="$(pg)"/>
            </go></anchor><br/><br/>';
            }
        }
if($uinb[0]>0)
    {
        echo '<br/><a href="index.php?ac=nbx&amp;sid='.$sid.'">'.$lang['inbox'].'('.$uinb[0].'/'.$tinb[0].')</a><br/>';
    }
echo '
    <a href="index.php?ac=upg&amp;sid='.$sid.'">'.$lang['upload'].' '.$lang['picture'].'</a>
    <br/><a href="index.php?ac=upv&amp;sid='.$sid.'">'.$lang['upload_video'].'</a>
    <br/><a href="index.php?ac=prt&amp;sid='.$sid.'"><img src="images/home.gif" alt="*"/>'.$lang['home'].'</a>
    </p>';
echo $xhtml?xhtml_footer():wml_footer();
?>

muchos gracias...

[arnage]

Try to change COUNT(id) into COUNT(*),
and things like

$whr = "WHERE a.spublic='1' "; 

into

$whr = "WHERE `a.spublic`='1' "; 

And for

$ord = "b.name"; 

because there is a sql command for ORDER which goes from the table, looks like this for example.

$query = mysql_query('SELECT * FROM `table` WHERE `id` = '.$id.' ORDER BY `table`.`row` DESC'); 

... so maybe that dot makes something to wait.

And default table settings are NOT NULL?

[something else]

looks to be an indexing problem i would try:

ALTER TABLE girate ADD INDEX (gid)

ALTER TABLE gicomments ADD INDEX (gid) 

[morse]

The problem lies with

$items = mysql_query("SELECT a.uid, a.url, a.adate, a.viewed, b.name, b.perms, a.isize, a.id, AVG(c.irate) AS ir, COUNT(d.id) AS nc, a.description, a.type FROM ((gallery a LEFT JOIN users b ON a.uid = b.id) LEFT JOIN girate c ON a.id=c.gid) LEFT JOIN gicomments d ON a.id=d.gid ".$whr." GROUP BY a.id  ORDER BY ".$ord." LIMIT $limit_start, $items_per_page");

You are joining the tables users, girate, gicomments and at the same time you are utilizing mysql Count and Average functions, its just excessive load on your MySql server.

[something else]

The problem lies with

$items = mysql_query("SELECT a.uid, a.url, a.adate, a.viewed, b.name, b.perms, a.isize, a.id, AVG(c.irate) AS ir, COUNT(d.id) AS nc, a.description, a.type FROM ((gallery a LEFT JOIN users b ON a.uid = b.id) LEFT JOIN girate c ON a.id=c.gid) LEFT JOIN gicomments d ON a.id=d.gid ".$whr." GROUP BY a.id  ORDER BY ".$ord." LIMIT $limit_start, $items_per_page");

You are joining the tables users, girate, gicomments and at the same time you are utilizing mysql Count and Average functions, its just excessive load on your MySql server.

its saving 6 querys to database though :/ although gicomments isnt used in above code ... but i should imagine it will be.