post-page

How many Queries are too many?

41
responses
by
 
on
July 12th, 2008
in
Blogging, General, WordPress

I have been asked this question quite a few times and I never have a perfect answer. It is obviously an important question to ask but it can be answered in many different ways based on preferences, need for plugins, optimization techniques used and a variety of other factors.

Weblog Tools Collection uses a lot of plugins and is very query heavy but the in spite of that, the front page uses 59 queries to generate itself. I think the default (on a vanilla WordPress install on the default theme) is 27 or something of that nature. The larger the number of queries, the slower the page is going to load and the more load you are going to put on your MySql server. All of the above is true if you do not use inline or regenerative caching mechanisms. The caveat on this blog is that its plugins and its MySql server are highly optimized (via inline caching mechanisms) to reduce load.

Larger number of queries on a WordPress blog are probably caused by plugins that have been installed. Plugin authors can have non-optimized and useless queries that either take a long time to execute or need a large(r) number of queries to acheive a simple(r) result. I suspect that one could follow the 80-20 rule with 80% of the queries on your blog being generated by 20% of the plugins. If you can identify the top hitters and either optimize or replace them, you will have a happier blog and a happier MySql server.

It is also important to remember that the number of queries might not be the only straw that is breaking the camel’s back. SQL queries can be poorly constructed and can put a tremendous drain on resources. I remember a certain recent comments plugin that made queries which scanned entire tables before producing a result. If you are concerned about the speed of you blog or the number of queries, start disabling plugins. You might be pleasantly surprised to find that a few plugins might be causing most of your headaches. Once you identify the suspect plugins, you can then either find replacements that are more oprtimized or find other ways of obtaining the same result.

How many queries does your font page generate? If you find efficiencies by disabling plugins, please tell us about the results.

heading
41
Responses

 

Comments

  1. Bobby Revell (1 comments.) says:

    I’ve used several plug ins for a tag cloud in my side bar. I recently started using simple tags, but like all the plug ins I’ve tried for this, it overloads my database. WP 2.5′s built in tag cloud seems to work best, though I still use simple tags to manage my tags.

  2. Baris Unver (17 comments.) says:

    How do we measure that?

  3. GaMerZ (31 comments.) says:

    Personally, I think any number less than 50 per page is fine. The perfect would be from 25 to 30 queries.

  4. Mark Ghosh (386 comments.) says:

    Baris: It shows up in the source code in most themes. Take a look at the HTML of your blog.

  5. Robert Kingston (1 comments.) says:

    Does WP Cache and Super Cache rely on any queries? If so, how many?

  6. tibc says:

    Baris: Try this in the footer

    queries.
    seconds.

    Credit to http://lastblogger.com/archives/339

  7. kahi (3 comments.) says:

    24, but caching some pieces of pages, like sidebar or footer.

    By the way, by results of my former research, the number of queries hasn’t much influence on the generation time. It’s correct, that when you turn off some plugins, you not only reduce count of queries, but you also stop it’s (probably unoptimized) code-processing. So I don’t think it’s super-important to watch (only) the db queries count… of course, when the number goes over 100, you know that something is not just all right.

    By the way (for second time), not only badly written plugins may cause huge press on database. Even for example the core function wp_list_authors has such unpleasant abilities…

  8. Mark Ghosh (386 comments.) says:

    the number of queries hasn’t much influence on the generation time

    That is a loaded statement. A page with 10 simple select queries with no results will load faster than a page with a single, complex joined query that returns 10,000 results. However that case is jaded and uncommon.

    In general, with similar types of queries, a larger number of queries will increase load times.

  9. Neil (30 comments.) says:

    Can wp-cache etc not actually render some queries inapt?

  10. kahi (3 comments.) says:

    In general, with similar types of queries, a larger number of queries will increase load times.

    Well, this is quite self-evident. I just wanted to say that the generation time is consumed not so much by queries to db as by the processing of data.

  11. Stephen R (24 comments.) says:

    I recently wrote an article telling plugin authors how to reduce database queries by putting their options into arrays. If you’re interested:

    Consolidate Options with Arrays in your WordPress Plugins

  12. Mark Ghosh (386 comments.) says:

    Kahi: We could debate this all day and be completely unproductive in our debate. If SQL queries are terribly written or performed needlessly, they are the bottleneck. If processing of data is done badly or in infinite loops, therein lies the bottleneck.

    You claimed that the number of queries does not have much influence on page generation times. Then you say that page generation time is consumed not so much by queries as by processing of data. While it can be true that page generation time can be consumed more by the processing of data and retrieving information from non-db sources, a larger number of queries can have a negative impact on page generation times than the same page with smaller number of queries. If the same plugin is rewritten with an optimized SQL query or the number of queries is reduced by more elegant code, the page generation times will be less and the blog will load faster. The more gross the SQL in the plugin, the more impact optimizations will have. That was the point of this post.

  13. Chewru Guru (3 comments.) says:

    Chewru has 112 queries loading in .65 seconds. We are running 33 plugins. Apparently the queries are very high given some of the thresholds you guys have posted here, but even UNCACHED, the site loads in 2.6s according to webwait. Is this more a server/cpu/load/host issue as well?

  14. Andy Skelton (1 comments.) says:

    Query count alone is not the key to blog performance. It’s useful but not without digging deeper (as you suggested). Some queries are slower than others, some code is terribly slow. I don’t worry about it, though, because in a high-traffic situation advanced caching reduces the average query count to near zero.

    Even if you have only ten queries, you can benefit from saving them to look at which ones take longest. Maybe you need to add an index or rewrite the query or save the results of expensive computations in an options row to be autoloaded with no additional queries.

    Speaking of autoloading options, it’s good to go through your options and see what useless stuff is being loaded from the database for every page load. (This alone is worthy of posts and plugins and wiki pages.)

    Finally, beware of anything that makes requests to other domains. HTTP requests aren’t counted anywhere but they can be much worse than MySQL queries: they make your blog depend on another site for its performance. Far too often these requests are the cause of a slow-loading page—even if they are only included in the HTML!

  15. Tim says:

    I’ve got 12 plugins installed, and my homepage reports 16 queries. :)

  16. kahi (3 comments.) says:

    @Mark Ghosh: I’m sorry for my English, I’m (it seems) not able to understandably explain what I think :-). It is very close to the post of Andy Skelton (#14).

  17. Mark Ghosh (386 comments.) says:

    @Andy: Firebug rulez when it comes to finding errant HTTP requests made to other domains from a page.

    @kahi: :-) I understood what you said but was pointing out the subtle differences. This is a hotly contested topic in many circles.

  18. Slevi (7 comments.) says:

    SQL queries usually aren’t the biggest point of concern, even with non optimized queries it tends to be a matter of a second or two on slowdown unless the mysql server is practically hanging up. HTTP requests and inefficient php work usually counts up for a lot more waste of time, sometimes going as far as a matter of minutes before the page ends up fully loaded.

    SQL optimization is nice, and definitely is something which shouldn’t be overlooked in case you don’t cache but do have a high number of incoming visitors but for speeding things up more can be found in more efficient php scripting and having a look at external sources slowing things down.

  19. paan (4 comments.) says:

    @comment 11
    the problem with loading options into array is that the httpd process will have a high memorry usage. For me right now, that is more of a problem than the db queries.

  20. Frederick (4 comments.) says:

    22 plugins and 76 queries on the homepage. Bad. I’m going to start optimizing. :)

    Wow; disabling the SEO Smart Links plugin reduced the number from 76 to 40. Honestly, though, with WP Super Cache enabled, I don’t think it affected the blog that much.

  21. Frederick (4 comments.) says:

    I think the main culprits are WP-PostRatings and All in One SEO Pack. Has anyone else noticed a high number of queries with these two plugins?

  22. George Serradinho (23 comments.) says:

    Hey guys,

    I want to thank Mark Ghosh for answering my email. I had from 200 to over 300 queries. They were wp-email and wp-print. I have the results on my website.

    got to http://www.serradinho.com/Blog.....ueries.htm

    Maybe someone else gets the same results.

  23. felipe.lv (5 comments.) says:

    35 queries on is_home()

    it might be a good idea to create a poll so we could have more data and get better stats… would it produce a normal distribution?

  24. Viper007Bond (91 comments.) says:

    29 queries on my blog for it’s homepage, but I can cut that in half with a persistent object cache. That results in stagnant data though, so it’s not worth it to me.

    My other site generates it’s homepage in 16 queries (could be less if I wanted) and then is cached by WP-Super-Cache as it’s basically Dugg weekly.

  25. GaMerZ (31 comments.) says:

    @Frederick WP-PostRatings used only 1 additional query if I rememered correctly as the ratings is loaded from Post Meta and the options are loaded from the Pptions table. Unless you are using Highest Rated, Most Rated etc. The most query I would say would be my WP-UserOnline.

  26. Rajesh (35 comments.) says:

    The more the number of queries the badder is the performance.That is very true.But when you have caching plugins enabled, the queries are almost nil until the page/post expires.Caching plugins help us to have more plugins in wordpress.

    But at the same time, plugin authors should be more responsible and write the most optimized code.

  27. Kirk M (3 comments.) says:

    Just to add my two cents worth here, I have 2 sites currently running on WP 2.6 beta 3.

    Front page of my 3 yr old personal blog = 33 queries. 0.527 seconds.

    Front page of my newer tech blog = 26 queries. 0.491 seconds.

    Both run 16 well known plugins including “WP Supercache” with an extra 3 on the newer tech site (both “Add to Any” plugins and Westie’s “pjw-wp-phpmailer-nosender” plugin he cobbled up for 2.3.1 and above).

    To the average surfer with an average high speed connection both sites have fast loading times unless one of the external statistic engines I use chokes or goes off line.

  28. Rajesh (35 comments.) says:

    The general rule should be, if you have good traffic to your site and you use many plugins(I assume most plugins are always query intensive)then you should have a caching plugin enabled whether it be wp super cache (my favourite) or any other that you prefer…I would even recommend caching plugins from day 1 of your wordpress blog.

    The only drawback is a few like stats plugins that depend on server sided scripting may not work properly…

  29. Ariejan de Vroom (2 comments.) says:

    If you’re looking to optimize the performance of your wordpress blog, you may also look at other methods than only optimizing queries.

    I wrote a piece on that a few days back: http://ariejan.net/2008/07/09/.....ress-blog/

  30. Kevin Neberman (1 comments.) says:

    I have 81 queries for a calendar plugin and it loads in less than .482 seconds. It really depends on the size of the query.

  31. Mr Gray (2 comments.) says:

    Thanks for this info – I was happy to discover I only had 18 queries.

  32. Graig (1 comments.) says:

    I was getting over 800 queries in 2 – 3 seconds!
    Turns out I was using a plugin “List Authors Widget” that, once deactivated, reduced that to 60 queries, which is a bit more manageable.

  33. junky says:

    A default installation of WOrdpress hast 19-24 Queries and its for me very slow.
    I installed Supercache and Php Speedy now the performance is amazing but the queries are still there.
    There are bette CMS Systems who solve that performance Problem for Instance Modx
    default installation of ModX has only ~ 4 – 7 Queries and if you click the link twice no query is used and that cms is very performant. I wanted change to ModX but then I’ve read that Modx is completely rewritten therefore I’m staying with WOrdpress and looking for another solution

  34. Binh Nguyen (15 comments.) says:

    Thanks for sharing this.

    And mine is currently having this:
    204 queries.
    0.816 seconds.

    If I disable all the widgets it can get down to
    184 queries.
    0.718 seconds.

    If I switch to the default theme it is
    165 queries.
    0.644 seconds.

    That’s the reason why I found this page because I want to know why it’s having so many queries.

    And I haven’t try to deactivate all plugins yet.

    If you don’t trust me, go have a look at the source code for this: http://cars.apmart.com

  35. Bilinen (1 comments.) says:

    Finally, beware of anything that makes requests to other domains. HTTP requests aren’t counted anywhere but they can be much worse than MySQL queries: they make your blog depend on another site for its performance. Far too often these requests are the cause of a slow-loading page—even if they are only included in the HTML!

  36. pete says:

    172 queries. 0.670 seconds.

    25 plugins

  37. riesurya (1 comments.) says:

    (from my bad english :( )
    keep watching on with how many queries made.
    relevancy between queries number with page load it may not significant, however too much query will take more resources on server and as the result it will take a longer time for page load.



Trackbacks/Pingbacks

  1. [...] How many Queries are too many?, in diesem beitrag geht man der Frage nach, wann sind denn bei einer WordPress-Installation zu viele Datenbank-Abfragen [...]

  2. [...] queries son las adecuadas? | ¿Cómo saber cuántas queries hace tu blog? | How many queries are too many? Escrito el 14.07.08 @ 7:03 pm | Categoria: Blogultura.com | | Written by: Alan Vargas [...]

  3. [...] How many queries are too many? Optimieren von SQL Querys in WordPress installationen [...]

  4. [...] have to say about various news. Be it release of WordPress 2.6 or if they are concerned about the amount of queries of WordPress or if they are raising their concerns that why old media doesn’t link to [...]

Obviously Powered by WordPress. © 2003-2013

page counter
css.php