MySQL is not using composite index with ORDER BY clause-CodePudding

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?

TIA

CodePudding user response：

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;

CodePudding user response：

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.