Are there any advantage sorting on indexed fields

1
votes

I want to perform a sort operation on some field. Is it advantageous making an index on that field. For example:

SELECT * FROM `users` WHERE `age`=33 ORDER BY `name`

In this query, I know that having an index on age is helpful, but would it be better if I maintain an index for name. Would there be a performance gain by indexing it. And the ORDER BY operation is frequently needed for other queries as well.

1
mysqlsqlindexing
Are there many users of age 33? A separate, additional index on "name" is probably not too useful, but maybe index (age, name). – Thilo

1 Answers

5
votes

An index on name alone would not be likely to help significantly with this query, but an index on (age, name) would.

While it's not entirely accurate, it's often instructive to think of an index as a list of rows sorted by the keys in the index (e.g, sorted by age first, then by name). In the case of your sample query, all the rows with age=33 would naturally come out of the composite index sorted by name, saving you from doing a separate sort. Having a separate index for name wouldn't help the same way.