Announcement

Announcement Module
Collapse
No announcement yet.

IN(...) ORDER BY ...

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • IN(...) ORDER BY ...

    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.

    Comment

    Working...
    X