optimize number of sql queries

[Ianedres]

Not sure why your particular forum is using so many queries; I would say that the normal range would be 15 to 25, and you are approximately three times that.

Try turning off the panels and use only the forum's recent messages (index.php?action=recent) and watch the count. Is it extremely high or out of the expected range of 10-15 queries? (My site reports 19 w/ 4 blocks enabled.)

[G6Cad]

From the image it looks like 3 instanses of google, 2 side blocks and one center

Please post a link to your site so we can see what you have there

[Teza]

Not sure why your particular forum is using so many queries; I would say that the normal range would be 15 to 25, and you are approximately three times that.

Try turning off the panels and use only the forum's recent messages (index.php?action=recent) and watch the count. Is it extremely high or out of the expected range of 10-15 queries? (My site reports 19 w/ 4 blocks enabled.)

With the recent posts block disabled (or the entire left panel disabled) I'm exactly in that range. So with that disabled I ran index.php?action=recent and it gives 54 queries, whereas index.php?action=forum gives 22 queries - reported by $context['load_queries']. Everything works perfectly except for the recent posts.

Is it at all possible that $context['load_queries'] is bugged maybe because of the many left joins in ssi_recentPosts() queries?

As for G6:

Since the site is in development, at the request of the site's owner I have to keep the URL to myself, but a screenshot of the site looks like this: http://i37.tinypic.com/15nx25j.png

Those google images are just placeholders. The 2 you see on the side is from the gallery retrieved with the following code:

Code Select Expand


global $scripturl, $db_prefix, $modSettings, $context;

$gal_columns = 1;

$gal_query = 'SELECT thumbfilename, ID_PICTURE FROM '.$db_prefix.'gallery_pic WHERE approved = 1 GROUP BY thumbfilename ORDER BY date DESC LIMIT 2';
$gal_result = mysql_query($gal_query);
if (!$gal_result){
if (mysql_errno() == 1146){
echo '<p />Error, no database found!<p />';
} else {
echo '<p />MySQL error:'.mysql_error().'<p />';
}
} else {
echo "\n".'<table cellspacing="0" cellpadding="0" border="0" align="center" width="100%">'."\n";

$gal_colcnt = 1;
echo "<tr>\n";
while ($row = mysql_fetch_assoc($gal_result)){
if ($gal_colcnt > $gal_columns){
echo "</tr>\n<tr>\n".'<td colspan="'.$gal_columns.'"><hr /></td>'."\n</tr>\n<tr>\n";
$gal_colcnt = 1;
}
echo '<td class="normaltext" align="center">'."\n";
echo '<a href="'.$scripturl.'?action=gallery;sa=view;id='.$row['ID_PICTURE'].'"><img src="'.$modSettings['gallery_url'].$row['thumbfilename'].'" /></a><br />'."\n";
echo "</td>\n";
$gal_colcnt++;
}
mysql_free_result($gal_result);
echo "</tr>\n</table>\n";
}

And the one in the center is just an image embedded into a forum post with [img].

Your experience with this type of thing tells me ssi_recentPosts() cannot add 47 queries, but the facts here tell me otherwise. If I have time later today I'll recreate that function to my needs, without the hack to get unique results, and without all the left joins in the original query.. we'll see.

[G6Cad]

I want to have a link to your site

[Teza]

Sorry, that is not going to happen. I realize that I was the one asking for help and You were the one taking time to help me and not giving a link doesn't really help at that, but I'm fine with the results accumulated in this thread. I can manage from now on.

Thank you

[Ianedres]

My experience with ssi_recentPosts (using just the 'echo' and not the array- not that it matters) lead to only two less queries per page view, with no blocks selected to be displayed.

http://www.bayoumx.com/forums/index.php?page=query_test&show_query=on

It may have something to do with how often your SQL server's cache is flushed.

Also, I do not get any result in $context['load_queries'] (even with it in global scope) within the php article...

[Teza]

For me print_r($context) added to the index.template.php right after the </html> bit prints out an extensive amount of data, and the last 2 entities in the array is:
    [load_time] => 0.262
    [load_queries] => 75

[Ianedres]

'load_queries' isn't added in the array within articles- which I wanted to provide, but just use the footer to view the queries instead.

At any rate, this is an SMF issue, as the SSI originates from there and the SQL query count isn't adversely affected by TinyPortal, outside of adding blocks using SQL calls and normal overhead.

Again, I would suspect a timing issue on the SQL server...  on my own, if I reload the page quickly, the query count can differ by 2 or 4 from not having the recent posts displayed.