[wp-hackers] SELECT DISTINCT .... ORDER BY not working....

[Stephane Daury]

On Nov 20, 2007, at 20:25, Matt wrote:

> On Nov 19, 2007 11:09 PM, Computer Guru <computerguru at neosmart.net>  
> wrote:
>
>> I was actually thinking about that; but it's kinda like wtf is the  
>> point
>> of
>> making this one custom query all super-fast when the rest of WP  
>> isn't?
>> I've
>> seen a lot of code there that could benefit from subqueries, views,  
>> and
>> other new MySQL features - but when we're supporting MySQL 3 none of
>> that's
>> going to happen.
>
>
> We're supporting MySQL 4, not 3 anymore...


Are subqueries guaranteed in v4 and up? Or are they dependent on the  
engine used (innodb, etc)?

Also re: the point: I see a lot of people talking about caching on the  
list (luv the subject), but there's a lot to be gained on the  
performance front by just maintaining your own "wordpress-wide-index- 
setting-SQL-script" based on the queries used most often on your site,  
including the ones in plugin/theme code.

That's truly only for people with ultra-high-access sites (or really  
sucky shared hosting ;-), but it can be a night and day difference,  
especially as the content grows. Regardless of your caching policy/ 
tools, the content has to be fetched once (think fast changing  
comments), so the faster that happens, the better off you are. It also  
really helps with crawlers, who go about and query pages that are  
often unlikely to be cached after a while.

Of course, having some fined tune indices bundled with the installer,  
based on the queries the code runs (at least the major ones) and not  
just the primary keys, would be a nice step forward being more  
efficient with our madness right out of the box. ;-)  Let's us be a  
bit more feature/stability minded at the code level, rather than worry  
about performance at every corner. Then again, being attentive at the  
latter level is always an appreciated plus.

Stephane