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.