'MySQL is not using composite index with ORDER BY clause

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

explain result of the above 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

mysqlsql-order-byquery-optimizationdatabase-performancedatabase-indexes


Solution 1:[1]

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`);

Solution 2:[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Rick James

Related Questions