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

[Kimmo Suominen]

And given that it is wp_comments we are talking about, performance
can easily be an issue with any site, so that's definitely worth
taking into account.

I was considering a sub-select as well, as was mentioned elsewhere
in this thread, but large temporary tables can also be a problem.
Your idea about using an indexed view sounds good.

On the other hand, it might be worth using a simple to setup solution
first while keeping an eye on the performance.  If the query is not
hit that often, its less than optimal performance might not matter.
I've found that to be the case quite often, and it keeps the manual
WP database maintenance between upgrades to a minimum.

Although, "documenting" your custom queries as views might be a nice
central control for them... :)

Best regards,
+ Kimmo
-- 
<A HREF="http://kimmo.suominen.com/">Kimmo Suominen</A>


On Mon, Nov 19, 2007 at 05:17:48PM -0500, Stephane Daury wrote:
> 
> Nice! But highly irregular, and definitely don't expect this to be an  
> indexed query.
> Although the second you start using things like group by, etc, indexes  
> usually have to be manually setup (for obvious reasons), so not a big  
> loss in practice I'm sure.
> 
> But hey, what counts is that you gave Guru what he was looking for,  
> and that scores high as a problem solver in my book! ;-)
> 
> S
> 
> 
> 
> On Nov 19, 2007, at 17:01, Kimmo Suominen wrote:
> 
> >That's an interesting bug with MySQL.
> >
> >This seems to produce the result wanted:
> >
> >SELECT comment_post_id
> >FROM wp_comments
> >WHERE comment_approved = '1'
> >GROUP BY comment_post_id
> >ORDER BY MAX(comment_date) DESC
> >
> >Best regards,
> >+ Kimmo
> >-- 
> ><A HREF="http://kimmo.suominen.com/">Kimmo Suominen</A>
> >
> >
> >On Mon, Nov 19, 2007 at 11:27:34PM +0200, Computer Guru wrote:
> >>Nope, not working....
> >>
> >>Expected result:
> >>
> >>493
> >>501
> >>229
> >>485
> >>....
> >>
> >>Actual result:
> >>501
> >>502
> >>500
> >>491
> >>....
> >>
> >>That's the same result I get w/ the original SELECT DISTINCT ...  
> >>GROUP BY
> >>query in my OP.
> >>
> >>
> >>On 11/19/07, Aaron Brazell <emmensetech at gmail.com> wrote:
> >>>
> >>>This works for me...
> >>>
> >>>SELECT DISTINCT comment_post_id FROM wp_comments
> >>> WHERE comment_approved = '1'
> >>>GROUP BY comment_id
> >>>ORDER BY comment_date DESC
> >>>
> >>>--
> >>>Aaron Brazell
> >>>Director of Technology, b5media
> >>>
> >>>skype: technosailor
> >>>phone: 410-608-6620
> >>>web: http://technosailor.com
> >>>
> >>>Everything contained in this email is confidential and stuff
> >>>
> >>>On Nov 19, 2007, at 4:18 PM, Computer Guru wrote:
> >>>
> >>>>SELECT DISTINCT comment_post_id FROM wp_comments
> >>>> WHERE comment_approved = '1'
> >>>>ORDER BY comment_date DESC
> >>>
> >>>_______________________________________________
> >>>wp-hackers mailing list
> >>>wp-hackers at lists.automattic.com
> >>>http://lists.automattic.com/mailman/listinfo/wp-hackers
> >>>
> >>
> >>
> >>
> >>-- 
> >>Computer Guru
> >>Director,
> >>NeoSmart Technologies
> >>http://neosmart.net/blog/
> >>_______________________________________________
> >>wp-hackers mailing list
> >>wp-hackers at lists.automattic.com
> >>http://lists.automattic.com/mailman/listinfo/wp-hackers
> >>
> >_______________________________________________
> >wp-hackers mailing list
> >wp-hackers at lists.automattic.com
> >http://lists.automattic.com/mailman/listinfo/wp-hackers
> 
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>