Re: [Semanticscuttle-devel] performance problem with mysql query

[Eric C <eco...@el...>]

Christian,

Thanks for the response.  I've updated the info on stack overflow, but
there is still a problem...

The query is well optimized, but it does not return the same results
as the original query.

For example I have 3 entries in table "sc_bookmarks".

bId           bModified      bHash
------------------------------------
2000-11-10    aaaaaa         ...
2011-12-12    bbbbbb         ...
2010-11-11    aaaaaa         ...
The query should return only row id 1 and 2. But it return all 3
records. Seems mysql doesn't like: ´GROUP BY B.bModified DESC, B.bHash
DESC´

I tried to change ´GROUP BY B.bModified DESC, B.bHash DESC´

to ´GROUP BY B.bModified, B.bHash GROUP BY B.bHash, B.bModified´

but I'm still getting all 3 records.

It works when I leave ´GROUP BY B.bHash´ but that prevents the index
from working.

http://stackoverflow.com/questions/3722253/help-removing-mysql-file-sort

Thanks,

 -Eric

On Sat, Sep 18, 2010 at 8:44 PM, Christian Weiske <cw...@cw...> wrote:
> Hello Eric,
>
>
>> Mysql is using a file sort on one of the queries used to generate the
>> homepage.... This is taking about 4 seconds of 100% cpu on my box...
>>
>> I'm trying to optimize it, but don't have the required experience.
>>
>> Here are the details:
>> http://stackoverflow.com/questions/3722253/help-removing-mysql-file-sort
>
> The query needs to be as follows:
>
> SELECT U.username, B.*
>  FROM sc_users AS U, sc_bookmarks AS B
>  WHERE B.uId = U.uId AND B.bStatus = 0
>  GROUP BY B.bModified DESC, B.bHash DESC
>  ORDER BY B.bModified DESC
> LIMIT 10
>
> and you should create an index on status+modified+hash.
>
> --
> Regards/Mit freundlichen Grüßen
> Christian Weiske
>
> -=≡ Geeking around in the name of science since 1982 ≡=-
>
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing.
> http://p.sf.net/sfu/novell-sfdev2dev
> _______________________________________________
> Semanticscuttle-devel mailing list
> Sem...@li...
> https://lists.sourceforge.net/lists/listinfo/semanticscuttle-devel
>
>