0

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

Salman A
  • 262,204
  • 82
  • 430
  • 521
Alex
  • 11
  • 3
  • What indices do you have on this table? – Jakub Matczak Oct 22 '18 at 13:32
  • ^^ Please post both the `EXPLAIN SELECT *...` for this query and also `SHOW CREATE TABLE profile` – Michael Berkowski Oct 22 '18 at 13:32
  • You just want to show one specific record at the top, and all other records in any other order? Why not just select that record and union with the rest of the dataset minus that record? – Josh Eller Oct 22 '18 at 13:35
  • This table contains all the profiles, there are multiple profiles with the country_geoname_id = 2635167. I need to show all the profiles with that geonameId first (ordered by Score), and after that, all the profiles with others geonameIds (also ordered by Score). I will edit the question in a minute with the EXPLAIN SELECT. – Alex Oct 22 '18 at 13:37
  • Maybe you can make an UNION : you make a first SELECT with a condition WHERE country_geoname_id = 2635167 then UNION an other select with a condition WHERE country_geoname_id <> 2635167. This way you will have what you want first, then the other – Mickaël Leger Oct 22 '18 at 15:17
  • What version of MySQL? – Rick James Oct 30 '18 at 04:01

1 Answers1

0

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
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • 1
    I thought about this solution, but the problem is that the ID parameter from the order is dynamic, it's a different ID depending on the route you are in. – Alex Oct 22 '18 at 13:49
  • Then add that information to the question. But if that is the case you cant make it work. As I show you on the link, the moment you use a function on the condition you cant use the index. – Juan Carlos Oropeza Oct 22 '18 at 13:53
  • You would need MySQL 8.0 to make an index that would work: `INDEX(isMyCountry ASC, score DESC)`. The simple workaround is to flip the meaning of `isMyCountry` so that both columns are `DESC`. Then any version of MySQL, together with `INDEX(isMyCountry, score)` will work. – Rick James Oct 30 '18 at 04:03