front page takes 50 mysql queries
-
Can anyone tell me what the standard number of queries for the front page are on a default install of bbpress?
I think mine are high because of my template hack for the latest reply link. (added: nope, tried removing it and no query reduction)
There has to be a way to cache that and reduce the number of calls by pre-fetching all the links for all the topics to be listed, instead of doing one call at a time…
-
Ah I totally forgot I’ve got visitor tracking which adds some calls but doesn’t explain why it’s more than a dozen calls by design.
If anyone’s interested, I’ve ported the “query diagnostics” plugin from wordpress so you can see each mysql call used, hidden as a report at the end of each page (for administrators).
<?php
/*
Plugin Name: bb-benchmark
Plugin URI: http://CKon.WordPress.com
Version: 0.10
Description: Prints simple benchmarks and mysql diagnostics, hidden in page footers.
based on Jerome Lavigne's Query Diagnostics for WordPress http://vapourtrails.ca/wp-plugins
Author: _ck_
Author URI: http://CKon.WordPress.com
*/
/* INSTRUCTIONS:
1. add this line to your bbpress config.php file: @define('SAVEQUERIES', true);
2. install & activate plugin
3. do a "view source" on any bbpress page to see hidden results at bottom
*/
function bb_benchmark_output() {
if (bb_current_user_can( 'administrate' ) ) :
if (SAVEQUERIES) :
global $bbdb;
echo "<!-- n === benchmark & query results === n ";
while($qposition < $bbdb->num_queries){
$qsubtime=$bbdb->queries[$qposition][1];
if ($qsubtime>$qmaxtime) {$qmaxtime=$qsubtime;$qmaxquery=$bbdb->queries[$qposition][0];}
$qtotal += $qsubtime;
$qposition++;
}
$timer_stop=bb_timer_stop(0);
echo @shell_exec("uptime")."n";
echo "query count: ".$bbdb->num_queries." nn";
echo "total query time: ".round($qtotal,4)." seconds nn";
echo "total page time: ".round($timer_stop,4)." seconds.nn";
echo "page render difference: ".(round($timer_stop-$qtotal,4))." seconds nn";
echo "slowest call was: ".$qmaxquery."n at ".round($qmaxtime,4)." seconds nn";
if (phpversion() >5.0 && function_exists(memory_get_peak_usage()) && function_exists(memory_get_usage())) {
echo "nn === memory usage === n";
echo "peak memory ".memory_get_peak_usage()." nn";
echo "total memory ".memory_get_usage()." nn";
}
// echo "nn === resource usage === n";
// print_r (getrusage());
echo "nn === mysql queries used === n";
print_r($bbdb->queries);
echo "-->";
endif;
endif;
}
add_action('bb_foot', 'bb_benchmark_output');
?>Eek – I’m kinda freaked out by how bbpress handles front page topics and appends the last poster’s name.
Two separate mysql queries are made for each name to get the data. Instead of retrieving the sequence of names all at once so it’s within the same table for speed. So if you have 50 front page topics with 50 different authors, beyond all the other queries will be 100 more queries.
That’s got to be addressed if bbpress wants to call itself lightweight and efficient. All those names could be gotten with two built up calls, regardless how many on the front page.
OMG. This is insane.
Go edit your front-page.php, forum.php and view.php templates.
Change
topic_last_poster();
to
echo $topic->topic_last_poster_name;
Cuts mysql queries in half. I went from 50 to under 20.
The data is already in the retrieved topics in memory, there’s no need to reload all the userdata.
all the same way it could be probably saved on:
topic_link()
topic_title()
topic_time()
and so on, but this, I guess, wouldn’t allow to hook any custom filter?
No, all those are properly cached and do not reduce mysql queries.
topic_last_poster() for some reason forces a new metadata reload and two new mysql queries, uncached. It’s meant to be able to be used outside loops so it basically has to. It can’t just peek at the entire topics table just returned from the database.
Any plugin that affects the username has already affected it on the write to the topics table. It’s already set for presentation. No need to hook it unless you are doing something really crazy. Even the “admin use display names” plugin will still work correctly.
It’s the only really easy optimization.
“topic_last_poster()” sticks out badly if you install the plugin I posted above and look at the results.
update: if you are really worried about filters you can either have a lightweight pseudo function or do it this way
echo apply_filters( 'topic_last_poster', $topic->topic_last_poster_name, $topic->topic_last_poster);
instead of just
echo $topic->topic_last_poster_name;
and that will apply any filters looking for it
I’ve applied this plugin and I’ve only got 11 queries on the front page in kakumei, 13 in my theme (with an onlinelist and latest blog post). Are you running the latest? If not, that’s the likely cause.
Hmm. Well I don’t think it’s an old theme but it might be another plugin hooking topic_last_poster(). I will have to dig.
Also, how many forums + topics are you showing on your front page?
I’ve got 8 forums and 35 topics with roughly 20 unique last poster usernames on the front page.
Not that it really matters, if you are only hitting 11 queries than it’s still quite efficient. What’s funny is it could be single digit queries except that bbpress tests that it exists by hitting the database with a useless query every time it starts.
Aha! The culprit is “Use Display Name” written by no less than the mdawaffle himself…
His quick coding on that didn’t deal take into account the reprecussions of grabbing the entire meta for every username.
So I guess this is a caution to anyone using that plugin.
5 forums, 30 threads, something like 10 dudes? But like you said it doesn’t really matter.
I think Use Display Name just wasn’t written in the days of efficient usermeta caching, before “mdawaffle” (a dig?) implemented it. Obviously not kept up to date, of course. You can report it on the plugins trac.
Put it in the devlist and TRAC because Automattic people are at WordCamp this week and off til Monday.
Trent
Actually he can’t really “fix” the plugin, it’s an overall integration failure which necessarily adds more mysql queries (just like the plugin needed to allow spaces adds more overhead).
Basically the output template will HAVE to use a difference function call than the “use display name” which fixes it everywhere else before it’s written back to the database.
It’s the cost of integration and yet another example of why bbpress isn’t magically better than any other forum for integration with wordpress.
(or just use my workaround to cut out the extra queries
ps. “mdawaffle” wasn’t a “dig” – isn’t that his nickname?
Oh how embarrassing, it’s “mdawaffe” without the “L” I keep inserting. My sincere apologises, it was completely unintentional.
But your workaround no longer gives you the display names, right? … So you cut out the penalty, but also lost out on any gain?
Can’t you do what you said earlier and use a hook to get all the display names at once?
Hehe, actually I thought calling him mdawaffle was pretty good ^^ Just when you kept doing it I was confused.
Well there’s no way around the fact that “use display names for admin” basically MUST fetch the display name from the wp_users table when asked for it – and if it’s not been accessed before it’s not cached so it must hit the mysql db directly.
However, since “use display names” forces the forums table to store the display name properly, there’s no need to run the last poster id through the routine again and force a fetch of the display name, it’s already in the username field for last poster.
The good news however is between that tweak, a couple other bits of fine tuning and the undocumented $bb->load_options = true; I was able to get the queries down to just 10 for the front page for visitors and 13 for logged in members. This includes an extensive number of plugins, including useronline trackline (not the simplified one here but a port from wordpress).
It’s never been faster and rather impressive
- You must be logged in to reply to this topic.