IN(...) ORDER BY ...

MySQL & MariaDB Other MySQL® Questions
1

Hi!

I have a very simple table:

CREATE TABLE users (
user_id MEDIUMINT( 7 ) NOT NULL ,
user_name VARCHAR( 30 ) NOT NULL
)

ALTER TABLE my_users ADD INDEX ( user_id , user_name )

I would like to select specified users ordered by their name!

SELECT * FROM users WHERE user_id IN (1, 2, 3, 11, 22, 33) ORDER BY user_name

Unfortunately i can’t rid of the filesort (
Explain says:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE my_users range user_id user_id 3 NULL 7 Using where; Using index; Using filesort

I have 18.610 rows in the table:

  1. Jane
  2. John
  3. Smith
    ect…
2

Don’t think there’s a way of getting round this as index leafs must be skipped in the second branch (user_name) due to using a range in the first.