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

[Stephane Daury]

Also, I might be stating the obvious, but you'll gain a lot of  
scalability by just setting a custom index on at least the sub-query.

S.


On Nov 19, 2007, at 17:21, Stephane Daury wrote:

>
> Yeah, that's what I didn't dare suggest given how sub-selects aren't  
> always available in MySQL, and wasn't sure if you wanted to  
> distribute your code (ie: a plugin or so)
>
> S.
>
>
>
> On Nov 19, 2007, at 17:18, Computer Guru wrote:
>
>> I got this to work as well (subqueries):
>>
>> SELECT distinct comment_post_id
>> FROM
>> (SELECT comment_post_id FROM wp_comments
>> WHERE comment_approved = '1'
>> ORDER BY comment_date DESC) AS TBL
>>
>> About the same performance as Kimmo's query, give or take 0.001  
>> seconds in
>> my quick testing (0.047 vs 0.046)
>>
>> On 11/20/07, Stephane Daury <wordpress at tekartist.org> wrote:
>>>
>>>
>>> On Nov 19, 2007, at 16:45, Computer Guru wrote:
>>>
>>>> I'm using the latest MySQL 5.1 build, and it's not there... :-(
>>>> There's a reason they say PostgreSQL is a better MySQL than  
>>>> MySQL :P
>>>
>>> I'm not biting that bait! The flame kind. Made that mistake  
>>> before. :p
>>>
>>>
>>>> I think the problem is that stupid MySQL is insisting on doing the
>>>> GROUP BY
>>>> or DISTINCT first, *then* doing the sort... (and I can't stick the
>>>> GROUP BY
>>>> after the ORDER BY)
>>>
>>> Yeah, I looked a bit more, through the suggestions listed in the doc
>>> page I pointed you to, but there's not to be done that doesn;t  
>>> involve
>>> jumping through a whole bunch of hoops...
>>>
>>> S.
>>>
>>>
>>>
>>>> On 11/19/07, Stephane Daury <wordpress at tekartist.org> wrote:
>>>>>
>>>>>
>>>>> http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html
>>>>>
>>>>> I'm not sure if it's available in (your version of) MySQL, but in
>>>>> other RDBMS such as Postgres, you use "DISTINCT ON" instead of  
>>>>> just
>>>>> "DISTINCT".
>>>>>
>>>>> Stephane
>>>>>
>>>>>
>>>>>
>>>>> On Nov 19, 2007, at 16:18, Computer Guru wrote:
>>>>>
>>>>>> Can someone *please* tell me what is wrong with this query!? I've
>>>>>> been
>>>>>> bouncing my head off the walls trying to get it to work right,  
>>>>>> but
>>>>>> to no
>>>>>> avail.
>>>>>>
>>>>>> Original query:
>>>>>> SELECT comment_post_id FROM wp_comments
>>>>>> WHERE comment_approved = '1'
>>>>>> ORDER BY comment_date DESC
>>>>>>
>>>>>> That works just fine. But the minute I try to get distinct
>>>>>> comment_post_id,
>>>>>> the sorting stops working:
>>>>>>
>>>>>> SELECT DISTINCT comment_post_id FROM wp_comments
>>>>>> WHERE comment_approved = '1'
>>>>>> ORDER BY comment_date DESC
>>>>>>
>>>>>> It filters comment_post_id just fine (only one comment per  
>>>>>> post) but
>>>>>> my
>>>>>> results are no longer ordered correctly!
>>>>>>
>>>>>> Using GROUP BY instead of distinct gives me the same un-ordered
>>>>>> output.
>>>>>>
>>>>>> Any advice would be mighty appreciated.
>>>>>>
>>>>>> --
>>>>>> 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
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> 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
>>>
>>
>>
>>
>> -- 
>> 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