While doing some performance tests on frappe/erpnext, we observed that frappe framework do not create index on
modified column. This causes full table scan on every render of the List view for each document.
Refer following query where 14344 rows are scanned for fetching 10 records ordered by modified (List view query)
After adding index on modified column, number of rows scanned are just 10 (page size)
We have developed a script which indexes this column as a
after_migrate hook. Refer commit add before after migrate hooks (#4829) · frappe/frappe@4411cd4 · GitHub in which
after_migrate support is added.
def modified(): tables_to_update = frappe.db.sql_list(''' SELECT table_name from information_schema.columns where column_name = "modified" ''') for table_name in tables_to_update: create_index(table_name, 'modified')
We wanted to know from the community if there is any other better place to add index on modified column whenever a new doctype is created.
This also needs to be fixed for all the existing documents in Frappe and ErpNext. The list view performance would significantly increase as the full table scan is eliminated.