Search code examples
mysqloptimizationfilesort

MySQL ORDER BY CASE optimization

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:

EXPLAIN SELECT

Solution

  • 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