Frappe - Performance - modified column not indexed

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 https://github.com/frappe/frappe/commit/4411cd46b4a5a21a899ce8b2b3f6fa9fa07120fe 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.

1 Like

This issue is already fixed in develop branch. Index is now created on modified column when table is created

2 Likes