Database index dropped after bench update?

1

Hi, Dear All
I have noticed one problem, to improve the performance for query the Item table, I have manually added the database index via running db SQL DDL statement. And it works perfectly which significantly improved the performance. However, I noticed the index will be dropped after running the bench update.
Which will need to run the DDL statement again to bring it back.
Is there a way to avoid it?
Thanks a lot.

2

Single column indexes are managed by framework and apps. So if doctype.json or meta doesn’t say a columns is supposed to be indexed, it will be dropped on next migrate.

This is not a bug, it’s a feature to keep indexes synced with schema.

You can add custom indexes using this command:

bench --site sitename add-database-index --doctype="Sales Order" --column="order_id"

Indexes added using this command are not dropped automatically because it created property setter internally to ensure it persists even if developers remove it from app.

feat: `add-database-index` command to add and persist custom indexes by ankush · Pull Request #23787 · frappe/frappe · GitHub (This command is not yet released, should be out in tomorrow’s release)

I don’t think this is still a good way to solve this problem, will think about other possible solutions.

4 Likes
3

Thanks a lot. I think the problem is that for standard doc type, there’s no option to customize it while this can be done for customized doc. Maybe it should be part of the customization option of the standard doc type.
Thanks again.

4

Would be nice if we can add composite index, via method on_doctype_update, on frappe/erpnext doctype , in custom apps

1 Like
5

You can directly create a property setter for the said field and enable indexing.