Posted 2 years ago #
I have been running a few tests on my wordpress site since noticing the number of database queries for each page load is over 100.
When i disabled the sidebar function wp_list_authors I noticed that there were 26 less queries now on each page load.
Should this function really need to make more than one query to the database?
Posted 2 years ago #
It will perform a query for each time it's called, if you call it 20 times, you'll get 20 queries ... that's to be expected..
Same rule applies for functions like wp_list_categories, and so on...
Add all those calls on top of the loop query, plus any custom queries, and it soon mounts up .. (recent posts widget?, that's another query).. you get the idea..
The alternative is to store the data in a variable and re-use that variable wherever you need the data, in turn you only perform one query, the initial one that gets the data to store in the variable.. (of course you'd need some understanding of variable scope to make any real use of it without taking away functionality or usability).
Posted 2 years ago #
you can lookup simple PHP caching and set the expire time for a day, or even a week. Or never and you can just delete the cache whenever you get new author. I mean....how often do you get new authors anyway?
I did something like this for many of the features on my site. Making your own caching script takes about 5 minutes once you find a good tutorial.
Posted 2 years ago #
Hi
Thanks for your feedback guys !
The 26 queries are being caused by just one call of the function (my theme doesn't call wp_list_authors 26 times!)
I understand that most WP theme functions such as wp_list_categories make a single database query each. What i am trying to identify is particular functions which seem to make too many queries in relation to the functionality they provide.
I am already using php variables and arrays for category lists and recent post lists, and have hard coded all the URLs in place of the get_bloginfo('url') and get_bloginfo('template_directory') functions. But this is only a solution when a function is called more than once - as it takes at least one database query to populate the php variables & arrays in the first place.
Caching is an option - but is less practical if you are not involved in the day to day running of the site. I may consider it if I can find a suitable plugin which would delete the cache automatically when a new author is added. But if it comes down to me having to write the script myself I would probably be more inclined to try and write an alternative list_authors function that works on a single sql query.
I still have to ask:
Is it usual for the wp_list_authors function to make 26 queries to the database?
I'm finding it hard to believe...
Posted 2 years ago #
It does seem to make one query per author:
Posted 2 years ago #
You're right - It does far too many database calls.
I'm trying to resolve the same problem. I'm also not keen on caching, as it's a bit of a brute-force solution to something that can essentially be solved with better coding.
Posted 2 years ago #
Wowzers - The latest revision on the function on that trac page is really complicated. But it looks like they have resolved this O(N) database queries issue (and then some!)
rooodini - you might comment on that trac ticket that the patch worked for you and decreased the database queries--that will help get that patch into the coming 3.0 version. Use your forum login/password there.
Thanks.
Posted 2 years ago #
Hi there MichaelH,
I hadn't actually tested it... I just looked through the code and it seemed to wrap a lot of stuff into a single query.
I've just given it a go with a patched nightly build, and it still appears to have the same problem. However, I might be doing something wrong! (first time I've tried a nightly build / patching wordpress)
Cheers,
Andy
Posted 2 years ago #
I count a total of 4 queries for the function, then +1 for each author returned by the function (6 total on my test site - added another author for testing, 5 initially).
EDIT: Added another to confirm, i now get 7 queries for 3 authors(+4 as mentioned above), simply to prove my point.
10 authors would be 14 queries ... just to simply confirm that there are several queries being made for this function.
Using the current trunk, which at the time of writing is revision : 13972
EDIT AGAIN: Here's the wp_list_authors queries that run on my test install (queries prior to that are other wordpress stuff, unrelated).
[12] => Array
(
[0] => SELECT user_id, user_id AS ID, user_login, display_name, user_email, meta_value FROM wp_users, wp_usermeta WHERE wp_users.ID = wp_usermeta.user_id AND meta_key = 'wp_capabilities' ORDER BY wp_usermeta.user_id
[1] => 0.00020480155944824
[2] => require, require_once, include, wp_list_authors, get_users_of_blog
)
[13] => Array
(
[0] => SELECT ID, user_nicename from wp_users WHERE ID IN(1,2,3) AND user_login <> 'admin' ORDER BY display_name
[1] => 0.00011396408081055
[2] => require, require_once, include, wp_list_authors
)
[14] => Array
(
[0] => SELECT DISTINCT post_author, COUNT(ID) AS count, ID FROM wp_posts WHERE post_type = 'post' AND (post_status = 'publish' OR post_status = 'private') GROUP BY post_author
[1] => 0.001133918762207
[2] => require, require_once, include, wp_list_authors
)
[15] => Array
(
[0] => SELECT * FROM wp_users WHERE ID = 3 LIMIT 1
[1] => 0.00012993812561035
[2] => require, require_once, include, wp_list_authors, get_userdata
)
[16] => Array
(
[0] => SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (3)
[1] => 0.00013995170593262
[2] => require, require_once, include, wp_list_authors, get_userdata, _fill_user, get_user_metavalues
)
[17] => Array
(
[0] => SELECT * FROM wp_users WHERE ID = 2 LIMIT 1
[1] => 0.00017404556274414
[2] => require, require_once, include, wp_list_authors, get_userdata
)
[18] => Array
(
[0] => SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (2)
[1] => 0.00012898445129395
[2] => require, require_once, include, wp_list_authors, get_userdata, _fill_user, get_user_metavalues
)... should give you a more visual idea of what's running and why (if the above makes sense to you).
Pay attention to key 2 in each array as this gives you clues about what functions are making the queries.
Posted 2 years ago #
Hi t31os_,
Thanks for the visual output, that's excellent! I ran similar tests, but outputting simply the number of queries.
By the looks of your visual output, the number of queries = 2*N + 3 where N is the number of authors. But perhaps I'm wrong - Hard to tell as there are only 2 non-admin authors here.
Examining the queries in your visual output suggests you are not using the patch above. I'd like to try with that - Can you tell me where I have to hook in to output this object array of queries?
Finally.. Do you consider this to be an acceptable number of queries? Personally, I don't think it's good for the number of queries to scale with the number of authors (that's what I meant by "O(N) queries").
Thanks!
Andy
Posted 2 years ago #
Well i think it's acceptable when you're relying on easy to use functions, which is pretty much what WordPress functions are, a method of convenience. I'd personally just write my own query, though i'd guess we'd never get away with less then two, one to get authors, another to look up post data for those authors, unless you wrap that all into a single query(hurts my head just thinking about it though).
The above is part of the output from $wpdb->queries , which is populated by setting SAVEQUERIES to true in your wp-config file..
http://codex.wordpress.org/Editing_wp-config.php#Save_queries_for_analysis
Posted 2 years ago #
Good tip regarding SAVEQUERIES in the wp-config! Thanks for that.
I guess if the equivalent wordpress functions for categories, posts etc share the same O(N) queries property, then it's fine. I'll check this now.
Posted 2 years ago #
I think there is room for improvement with the function though, doing this for each user is a bit un-necessary.
SELECT * FROM wp_users WHERE ID = 3 LIMIT 1That is the query made by get_userdata, so perhaps instead of calling get_userdata for each author, instead it should be just running a single query that gets all the required authors in one go.
Same could said for this one.
SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (3)Which in turn i think again is a query created by get_userdata.
Room for improvement though i think, it's just a matter of someone with some time on their hands sitting down and figuring out how to improve it exactly.
Posted 2 years ago #
The SAVEQUERIES definition saves the database queries to a array and that array can be displayed to help analyze those queries. The information saves each query, what function called it, and how long that query took to execute.
This topic has been closed to new replies.
