Which indexes can i create for relational fields

Hello ! i’m a beginner with this subject, and i didn’t find the solution for my problem :

I got a query that get post (named pushs in my project) by author’s slug, and by chronological order, with a limit of 5.

pushs?author.slug=${this.user.slug}&_sort=createdAt:desc&_start=${this.userPush.paginate}&_limit=5

When i call this query, in production environment / database, this query is taking so long time, like 8-10s. I did my research and it seems it’s because i have 800+ documents in my collection, and mongo is doing a “collectionScan” to get the push with the good authors.

so i’ve read that to get better performance, i have to create an indexe. So to me, it’s relevant to make an indexe by “author.slug”, because it’s how i call the collection, with the query above . i created it with this command

db.pushs.createIndex({"author.slug": 1})

But with this one, nothing change, the query is taking the same time? what did i wrong ?

Here what a “pushs” looks like :

image

Hi @ImJustLucas and welcome to the community!!

To have better understanding on the issue being seen, could you please confirm with the following details which would help in reproducing the issue in local

  1. Can you specify the sample data for which query is being applied for as the index specified is not seen in the screenshot attached.
  2. The query being used in MQL format.
  3. The output received for the current query being used.
  4. The output for .explain(“executionStats”).
  5. The deployment being used(replica set/ sharded cluster/ stand alone)
  6. The MongoDB version being used.

Also, seeing the field __v:0, are you using mongoose by any chance? I’m curious if the author.slug field is the result of a mongoose populate() call ?

Best Regards
Aasawari

1 Like

Hi @Aasawari , thanks you for the answer, and sorry about this late response (i was in school week :smiley: )

1- here a screenshot of a author document (it is called users)

for the rest, i’m using strapi for my backend, so idk where i can find this informations, it seems it do it for me :frowning:

but there is my question, can i create a index of the pushs collection, order by author.slug, where author is a another collection and slug a fields of a user ?

if yes, how ? because it seems the db.pushs.createIndex({“author.slug”: 1})` don’t work, don’t increase performance on my query :smiling_face_with_tear: