[wp-hackers] Pull Latest Post from Each of 6 Categories

[Mike Schinkel]

"Austin Matzko" <if.website at gmail.com> wrote:
> First, what I suggested has no advantage over what Nathan's 
> already doing, except a trivial one: it lets you use the 
> regular Loop.  

Acknowledged.

> I am arguing against using MySQL directly, not suggesting 
> that my way is better than what he's already doing.

Fair point. OTOH I'm challenging the conventional wisdom that it is always best to use the WordPress API. In most cases I agree that use of the WordPress API is best, especially in plugins hosted on Extend but I'm not convinced that it always makes more sense to use the WPAPI instead of hardcoding SQL.

> Second, if Nathan weren't using WP-Super-Cache I might agree 
> with you. But since he is, I think using the WordPress API is 
> going to be less system-intensive.  Most WordPress performance 
> problems occur at the database level.  By using the WordPress 
> API and caching, he can dramatically reduce the total number 
> of database queries and thereforemoverall system load.

This I won't debate and generally agree. OTOH I do feel that caching is often used as a bandaid for poor design so I don't think caching alone should be used as sole rationale for coding sites that performing poorly.  Minimally it makes it much more painful to develop and debug a site when performance issues are solely resolved with caching. 

> Reducing the role of the database is usually the best 
> medicine for WP performance ills.

Seems we agree there in concept if not in execution.

> When you query posts using the WordPress API, your caching 
> system (such as WP-Super-Cache) gets each post's data in a 
> standard way that it can use beyond the immediate query.
> 
> WP_Query caches each queried post's data, so it's available 
> when requested elsewhere.  In contrast, caching a direct 
> query's results with an arbitrary key (as in Paul's example) 
> will not cache the individual posts in a manner that WP can 
> use. So it means that WP will have to make more queries to 
> get that data.

So doesn't caching still work when using posts_where and posts_join hooks?  I'm asking because I don't know for sure.  If yes, that that justification for not modifying SQL goes away.

> That assumes two things I doubt: that subqueries in general 
> perform significantly better and that any such queries are 
> better than cached WordPress API calls.

This is where we diverge; I'd rather start with a more peformant site before I have to rely on caching which itself has issues and thus is not a panacea.

>> Nathan can write code to anticipate this and fall back in case of an issue.
> I don't know how that's possible using the direct database 
> queries in this thread.  

Nathan (or any developer) could store the current version of WordPress into meta and then test it before running the query. If the version has been updated since first stored into meta then run the slower query and send a one-time email to the admin with a notice. If the version is the same that is in meta run the faster one. Also give an admin console option to allow the admin to choose to use the faster one again if they want.

That general pattern can allow for the use of performant SQL w/o concern for potential breakage when WP is updated in the future. Yes, it will run slower but that's what it would have done anyway if you didn't use the performant SQL.

> But to be clear, I'm not saying never directly query the 
> database; just don't do it when you don't need to.

We agree here, we just disagree whether this use-case presents a "need" or not. :-)

> The difference is that get_posts() takes care of the defaults.  
> In my example, I passed two arguments to get_posts(): the 
> category id and the number of results I wanted.  To make a 
> similar MySQL query directly, in addition to those I would 
> have to specify post date, order, type, status, term taxonomy, 
> and probably something else I'm not thinking of.  That means 
> more opportunities to mess up something.

Fair point. For me being more familar with SQL and less familar with get_posts() (having made many mistakes with it) I'd take my chances with SQL. OTOH, Nathan with his admitted lack of SQL knowledge might well be better off with get_posts().

ALL THIS SAID, maybe there is a good argument to be made for improving get_posts(), or providing an alternate, such that we can handle more complex SQL using the WordPress API?  I would propose some things here but having been bitten several times when trying to help with the core I don't think it's something I'm likely to take the lead on although if others are making an earnest effort I will certainly offer to help.

-Mike Schinkel
Custom Wordpress Plugins
http://mikeschinkel.com/custom-wordpress-plugins