Re: How to get rid of 'filesort'?

[Rick James]

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.