MySQL ORDER BY CASE optimization

Question

I looked over the internet, but I couldn't find a solution for this particular query. I have this query:

SELECT *
FROM profile
ORDER BY CASE WHEN country_geoname_id = 2635167 
              THEN 1 ELSE 0 END DESC, 
         score DESC

I want to know if it's possible to optimize this query by avoiding the filesort. I created an index for the columns (country_geoname_id and score), but it didn't help.

EXPLAIN SELECT:

Answer 1

You make your order condition not sargeable when put it inside a function.

What makes a SQL statement sargable?

if you want use index create an aditional boolean field isMyCountry and create an index for it

Then your query became:

SELECT *
FROM profile
ORDER BY isMyCountry, 
         score DESC