MySQL is not using composite index with ORDER BY clause

Question

I have a table user that looks like this

id | first_name | last_name | org_id

This table has few million entries.

I want to run the below query with an exact match and an order by clause

     select * from user 
     where org_id = "some id" 
     ORDER BY first_name asc, last_name asc 
     limit 100;

I also have the following indexes:

org_id
org_id, first_name, last_name

When I run an explain on this query, mysql uses org_id index instead of the composite index on org_id, first_name, last_name.

This is the output of the explain query

I can see in the possible keys sections where mysql evaluates the composite index but still does not uses it.

I have read several answers like this one which says that composite index should be used here.

This query is really slow in case the match is really. Any idea

why mysql is not using the composite index?
How can I speed up this query?

Edit 1: Here is the table DDL

CREATE TABLE `user` (
  `organisation_id` bigint(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `id` bigint(20) NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `middle_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `organisation_id` (`organisation_id`,`email`),
  KEY `idx_first_name_last_name` (`first_name`(32),`last_name`(32)),
  KEY `idx_organisation_id_first_name_last_name` (`organisation_id`,`first_name`(32),`last_name`(32)),
  CONSTRAINT `user_org_fkey` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

TIA

Update: Updating the index as mentioned by Liki solved the issue for me

user_8309113 · Answer

I'd think that optimizer would select the composite index as you expected. (But it's not in your database)

I tested the same situation on my test DB, but it selects the composite index.

Fortunately, there is an index hint in MySQL for optimizer decisions.

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

Example:

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

Finally, could you try to run your SQL with the following hint?

select
  *
from
  `user` USE INDEX (your_composit_index_name)
where org_id = "some id"
ORDER BY first_name asc,
  last_name asc
limit 100;

Edit 1: Index fix

Please fix your index. Your key lengths are defined as 32 in index idx_first_name_last_name, but they should be 255 lengths.

ALTER TABLE `user` DROP INDEX `idx_first_name_last_name`, ADD KEY `idx_first_name_last_name` (`first_name`, `last_name`);

user_1766831 · Answer

DROP your INDEX(org_id), it may be getting in the way of using the better INDEX(org_id, first, last). If that helps, it will add more evidence of this gross optimization flaw.

MySQL is not using composite index with ORDER BY clause

2 Answers