MySQL Forums
Forum List  »  Optimizer & Parser

Re: How to get rid of 'filesort'?
Posted by: Rick James
Date: March 01, 2010 11:27PM

Is the real problem that the query takes "too long"? How long? The "rows" is quite small in all three rows of the EXPLAIN.

SHOW TABLE STATUS; -- want to see how big the rows and tables are.

Hmmmm... I see 3 semi-redundant FULLTEXT indexes on s3_shop_product. Have you determined that one won't do?

I see several TEXT fields, are any of them in the SELECT /*...*/ ? If so, then usage of a MEMORY tmp table would be impossible. Rearranging things to find the id via a subquery might facilitate the bulk of the work being done via MEMORY.

STRAIGHT_JOIN could be used to force it to start with s3_shop_product. But, since it chose not to, I suspect that would be slower.

Options: ReplyQuote

Written By
February 28, 2010 08:51AM
March 01, 2010 04:51PM
Re: How to get rid of 'filesort'?
March 01, 2010 11:27PM
March 02, 2010 04:56PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.