Best approaching for adding custom Indexes?

Questions and Answers Database-MongoDB
1

I’m wondering if there are any suggestions for managing a custom index on MongoDB with Strapi?

Essentially I want to do some slightly more advanced querying via Mongoose which require certain indexes to be created beforehand. I’m happy to go do this on the database directly, but I’ve noticed that if I do this next time Strapi loads it disappears. I’m currently not 100% sure if this is Mongoose or Strapi removing it. Firstly - any ideas why this might be happening, though I’m about to dig through more docs.

If I can’t find a way to suppress this, what would be the best way to run some code on initialisation that has access to the Strapi models? I appreciate this is by far the worst way, as now I’ve got to incur index creation times too.

1 Like
2

You can add index directly in /models/{name}.settings.json by adding index: true to the attribute. (for Mongo only, check Attributes doc)

I’ve added also some combined indexes manually, and they do not disappear after restart. Can you make some tests with a fresh project? @DMehaffy Can strapi delete indexes on mongo during bootstrap? As I never noticed that happening.

3

Unfortunately your suggestion on adding an index in a model doesn’t work for me. MongoDB offers a lot of functionality - but often this requires a custom index. So in this case I need a 2dsphere index otherwise I simply can’t query the data via Mongoose because it’s not valid to make a geographic query without first having the index.

Here’s the output of Mongoose during bootstrap. You can clearly see on that last line my index being dropped. At the moment I’ve no data, so generation will be super quick, but later on it could cost a not insignificant amount of time to regenerate, so ideally I wan’t Strapi to just leave it alone.

❯ npm run strapi dev

> traders@0.1.0 strapi /home/ian/src/traders
> strapi "dev"

Mongoose: companies.createIndex({ email: 1 }, { unique: true, background: true })
Mongoose: trade_types.createIndex({ name: 1 }, { unique: true, background: true })
Mongoose: strapi_role.createIndex({ name: 1 }, { unique: true, background: true })
Mongoose: strapi_administrator.createIndex({ firstname: 1 }, { unique: false, background: true })
Mongoose: users-permissions_role.createIndex({ type: 1 }, { unique: true, background: true })
Mongoose: users-permissions_user.createIndex({ username: 1 }, { unique: true, background: true })
Mongoose: companies.listIndexes()
Mongoose: faqs.listIndexes()
Mongoose: teams.listIndexes()
Mongoose: tags.listIndexes()
Mongoose: why_blocks.listIndexes()
Mongoose: trade_types.listIndexes()
Mongoose: core_store.listIndexes()
Mongoose: strapi_webhooks.listIndexes()
Mongoose: strapi_permission.listIndexes()
Mongoose: strapi_role.createIndex({ code: 1 }, { unique: true, background: true })
Mongoose: strapi_administrator.createIndex({ lastname: 1 }, { unique: false, background: true })
Mongoose: strapi_administrator.listIndexes()
Mongoose: users-permissions_role.listIndexes()
Mongoose: users-permissions_permission.listIndexes()
Mongoose: strapi_role.listIndexes()
Mongoose: upload_file.listIndexes()
Mongoose: users-permissions_user.listIndexes()
Mongoose: companies.dropIndex('geo')
4

Can you share the /models/{name}.settings.json file please.

5

Also please name the fields that you want to be indexed. As I want to try them on my local env.

6 
{
  "kind": "collectionType",
  "collectionName": "companies",
  "info": {
    "name": "Company",
    "description": ""
  },
  "options": {
    "increments": true,
    "timestamps": true,
    "draftAndPublish": true
  },
  "attributes": {
    "name": {
      "type": "string"
    },
    "trade": {
      "model": "trade-type"
    },
    "address": {
      "type": "string"
    },
    "postcode": {
      "type": "string"
    },
    "email": {
      "type": "email",
      "unique": true
    },
    "phone": {
      "type": "string"
    },
    "mobile": {
      "type": "string"
    },
    "location": {
      "type": "json"
    }
  }
}

I want to create a 2dSphere index on the location field.

1 Like
7

So, everything is quite simple, modify your attribute in /models/{name}.settings.json

"location": {
      //add this one
      "index":"2dsphere"
    }

Note: "index":true adds the regular ascending index, this is why your manually created 2dsphere was deleted during bootstrap.

Available methods for indexes (only for MongoDB!):

 "index":1      // will create regular ascending index
 "index":-1     // will create regular descending index
 "index":"2dsphere"   // will create 2dphere index

Result:

image

2 Likes
8

Thanks @sunnyson, that’s really helpful. Is it possible to create more complex indexes (e.g. compound) using a similar mechanism?

9

@sunnyson has the correct method here, do note that currently the index creation only applies to MongoDB at the moment. We need to add some handling for SQL databases that support indexes like this.

10

Unfortunately I don’t know any method how to create compound indexes from schema.

Also, refering to that situation, it deletes missing indexes only on development env.

https://github.com/strapi/strapi/blob/master/packages/strapi-connector-mongoose/lib/mount-models.js#L275-L281

11

Ah, that explains why the indexes are vanishing. I’ve been trying to develop my service at the same time so I’ve created them and then it’s restarting whenever I save a change to the code.

At least in production I can optimise with a compound index later.

12

Yes, in production it will keep all the compound indexes. So just create them manually with no worries.

13

Thank you for the solution, I have made some test and found 2 issues.

  1. “index”: true will not applied to “relation” field, i am not sure kind of this index if will help for the performance, but i used it

  2. after added “index”: true, when we add/update/delete fields for that model via “Content-Types Builder”, it will shows “An error occurred” when save

So, I applied all the indexes for production manually via command line at the moment until a better solution come out.

1 Like