optimize number of sql queries

[Teza]

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

Code Select Expand

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

[IchBin]

What the heck do you have on your frontpage that requires 70+ queries?

[Teza]

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

[Teza]

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:

Code Select Expand

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

[IchBin]

I think you have something else wrong. I added that code to my site, and on the frontpage it only added 3 extra queries.

[Teza]

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

[IchBin]

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.

[Teza]

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.

[G6Cad]

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 )

[Teza]

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.