CODEWITHSUNDEEP
sqlindexingsql-order-by
perak
perak

Reputation: 1340

Indexing column used for order

Lets say I have following query:

SELECT * FROM table WHERE id = 1 ORDER BY name

Is it better to add multi-column index (id and name) or separate index for name?

Upvotes: 4

Views: 70

Answers (1)

Andomar
Andomar

Reputation: 238106

That depends on the cardinality of id. If it has a high cardinality (meaning, few rows share the same value of id) an index on id is a good idea. The database will do a seek for id and look the few matching rows up in the base table.

If half the table has the same id, the query will result in an index scan. Note that since you're using *, a multi-column index would not be used unless it contains all columns in the table. So this can be made faster only with an index containing all columns that is sorted on (name).

At the end of the day, it's often best not to create an index at all, until you encounter specific performance problems. The specifics will allow you to test & measure whether your proposed index will work. That's almost always better than trying to guess the right indexes.

Upvotes: 3

Related Questions