mysql index usage question

Question

I have a simple query that looks like

select id, name from mytable where age=28 order by name

the EXPLAIN output confuses me, I got

possible_keys : age
key : name

Does that mean mysql finally decided to use the index 'name' to fetch all the rows? Is the age index used or not? How many indexes are used in this query? If it's the name index that mysql finnally chooses to use, what happened to the 'age' index?

GolezTrol · Accepted Answer · 2011-07-04 14:05:53Z

1

Indeed, name is used and age is not. Best solution is to have a combined key on both age and name (in that order). That way, queries can make use of this index when sorting and filtering on both columns.

answered Jul 4, 2011 at 14:05

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shawn Over a year ago

Does that mean in the case that the indexes in where and order by clauses are different, mysql will always pick one and only one to use unless I made them a combined index?

GolezTrol Over a year ago

I wouldn't say 'always', because I'm not sure. But usually this is the case. MySQL tends to use a single index, and tends to use the wrong one unless there is only the one perfect index. Sometimes MySQL merges two indexes on the fly, making it even slower.

Shawn Over a year ago

If the 'age=28' aren't used, does that mean my query will probably be a full table scan, since there's no index to reduce the rows to be scanned coz name is just for sorting?

GolezTrol Over a year ago

It seems so, although MySQL might decide otherwise when the number of record in the table grows. I assume it is relatively small now (less than a few thousand records)?

Shawn Over a year ago

Thanks for you help. I know what to do next now. Appreciate it.

Collectives™ on Stack Overflow

mysql index usage question

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related