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
>
>
|
