Hi!
I use SMF 1.1.6 with Tinyportal 1.0.5b1 on Gentoo Linux and when I was fiddling around in SMF's $context variable I noticed that loading my frontpage uses 75 queries. 72 if I load it as a guest. These numbers are horrific. I would like to drasticly reduce it, if possible to half of that, seeing how this very page of the TP forum uses 30-40.
My theme of choise is newdef 1.1.2 and my frontpage has the standard top section with a logo and the greeting and searchbar. I have the left panel enabled with 3 blocks on it, latest topics, latest posts and latest images in the gallery, they all use 1 single query to execute. The main area of the frontpage displays topics from the newsboard.
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
I'm hoping for a solution where by inserting
if (isset($context['TPortal']['frontpage'])) {
//we need this for the frontpage
} else {
//we dont need this for the frontpage
}
blocks of codes like this, I could reduce the number of queries for the frontpage. If anyone could show me a few examples or give me a list of things I could check out in the TP / SMF code if my setup really needs it or not, I think I could get the work done on my own.
Any help would be much appreciated
What the heck do you have on your frontpage that requires 70+ queries?
Just what I described, nothing else, really. What you can't see on the screenshot is 3 more articles. There's 5 of them total. Then the left panel with 3 blocks and the header area.
Unless $context['load_queries'] lies, these generate the 70+ queries.
PS: I have a couple of mods in the forums, but they are for viewing single posts and resize big images and stuff like that. SMF Gallery Pro is also installed but that shouldn't give 30 or so queries for the frontpage..
Ok, I was going through ALL the features to see which one when disabled drops down the number of queries and shocker...
The recent posts block which has the following code was responsible for 45~ queries:
$messages = ssi_recentPosts(16,"","","data");
$output = '<ul class="recentposts">';
$i = 0;
$j = array();
foreach($messages as $msg) {
if (!in_array($msg["topic"],$j) && $i < 8) {
if (substr($msg["subject"],0,4) == "Re: ") {
$newsubject = substr($msg["subject"],4);
} else {
$newsubject = $msg["subject"];
}
if (strlen($newsubject) > 25) {
$newsubject = substr($newsubject,0,23) . "...";
}
$output .= '<li><a href="' . $msg["href"] . '">' . $newsubject . '</a></li>';
$i++;
$j[] = $msg["topic"];
}
}
$output .= '</ul>';
echo $output;
It fakes "unique" threads by loading the latest 16 and selecting at most 8 which have unique thread id. Any ideas why it uses 72-25 queries? Or why it makes $context['load_queries'] say it does.. I don't have an easy way of confirming this in actuality but I assume it doesn't lie.
I think you have something else wrong. I added that code to my site, and on the frontpage it only added 3 extra queries.
Well with that single box disabled in the block's manager the value in $context drops to 25, with that enabled it goes up to 72 again. I check it 3 times and I also checked what that function does and it's unmodified and appears to be doing ... not 47 queries. Something's really funky is going on here...
Well, there's not much that I can think of to do. Being that the code calls the SSI function, TP really does nothing to modify it.
Ye, thanks for the help, I'll try to recreate that function on my own, it fetches too much data for my usage anyway.
Thank you once again.
Seen this before, and can tell you that it is the google adds you have, remove them and look at the number of queries
Also, how many forumposts do you have set to show on the pageload ? Every one have atleast 2 queries ( show post and show new image )
I don't have google ads (?). As for forumposts, I show 8 of them but since ssi_recentPosts() doesn't return unique threads I have to fetch 16 of them to hopefully get 8. So that would kinda explain most of the queries, but 16x2 still misses quite a few to 47.
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.)
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
Quote from: Ianedres on October 13, 2008, 09:49:02 AM
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:
Quote from: Teza on October 13, 2008, 12:00:11 AM
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:
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.
I want to have a link to your site
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
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...
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
'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.