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

[Stephane Daury]

Very sorry about the staggered posting, but I just  flashed on  
something I think you might wanna consider.

Since you mentioned you're under MySQL 5.x, you could consider setting  
up and indexing (if possible) a pre-sorted view instead of using a  
subselect.

http://dev.mysql.com/doc/refman/5.0/en/views.html

CREATE VIEW whatever_view_name AS
SELECT comment_post_id
FROM wp_comments
WHERE comment_approved = '1'
ORDER BY comment_date DESC;

Again, it's just in the spirit of scalability, and I guess it's not  
any more subject to *breakage* at upgrade time than writing your own  
queries.

'k, I'll shut up if I have another idea now. ;-)

S.



On Nov 19, 2007, at 19:55, Stephane Daury wrote:
> 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.
>
> 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)
>>
>>
>> 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
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers