Fastest way to sort on multiple count?

[eddy_turbox (Edgar H)]

Morning everyone,

Recently been wondering which is the most optimal way to sort on multiple fields?

At the moment I’ve got indexes for match clauses, but nothing on counts. I’m only using a single column on sorting and everything seems to be fine.

However, will there be any caveats if I start sorting on multiple columns (basically due to a design on being able to filter multiple columns on a table)? i.e. { count1: -1, count2: 1, count3: 1, count4: 1}

Is that something which is better done in memory once the results are retrieved?

Thanks in advance!

[Prasad_Saya]

Hello @eddy_turbox,

You can sort on multiple fields. This sort operation can be supported by an index on multiple fields. An index on multiple fields is referred as a Compound Index. Also, refer this in the documentation for examples and some related scenarios:

Use Indexes to Sort Query Results — MongoDB Manual

---

@eddy_turbox

Is that something which is better done in memory once the results are retrieved?

It is better to sort the data before it is retrieved into your application. The query filter and sort and any other operations are applied on the database server and you get the result in a single call. Indexes can be defined to support the query filter and sort operations. With proper study and planning you can possibly use an existing index or a compound index or modify an existing index to support your queries with sort.

You can generate a Query Plan for a query and see how an index is applied for the filter and sort operations. The db.collection.explain method can be used for this purpose.

[eddy_turbox (Edgar H)]

Thank you so much for the fast response @Prasad_Saya !

Regarding indexes, does it affect performance much having a compound index for sorting and another one for filtering/matching?

[Prasad_Saya]

@eddy_turbox

does it affect performance much having a compound index for sorting and another one for filtering/matching?

Having indexes doesn’t affect performance, in general, they are meant to improve performance of query filtering and sorting operations. Having lot of indexes for a collection can affect write performance - this is because when a document is written (inserted, updated or deleted) the indexes are also updated. Also, indexes need to be in RAM memory for them to be effective.

That said, you can have multiple compound indexes and these can be used as needed for filtering and/or sorting operations. Make sure that an appropriate index is in use for an operation by generating and verifying a query plan on the query.

Also, see this topic on Sort and Non-prefix Subset of an Index.