Frappe - Performance - Convention Gap - Index not used

Dear all,
By default, Frappe adds a name column in each doctype and this column is indexed. However there is also convention where the same document contains another column with the same data. E.g.

company_name in tabCompany
country_name in tabCountry
item_code in tabItem

In Frappe/ErpNext code there are queries on columns like above throughout instead of name column. This essentially means indexes are not getting used causing slow execution.

e.g.

where item_code = xxx

instead of 

where name = xxx

In fact in all doctypes where a particular column is set in autoname field, should be indexed by default.

We have created these indexes via script in after_migrate hook as follows -

def field_name():
    doctypes_to_index = frappe.db.sql('''
        SELECT
            name,
            autoname
        from
            `tabDocType`
        where
            (issingle = 0 or issingle is null or issingle = "")
            and (istable = 0 or istable is null or istable = "")
            and autoname like "field:%%"
    ''')
    for dt, field in doctypes_to_index:
        field = field.split(':')[-1]
        if field:
            try:
                create_index('tab{dt}'.format(dt=dt), field, True)
            except IntegrityError as e:
                if int(e[0]) == 1062:
                    print('Duplicate values for field {field} in doctype {dt}. This index will be created in next migration after removing duplicates'.format(
                        field=field,
                        dt=dt,
                     ))
                else:
                    raise

Is there a better place to add this logic? Basically any field marked in autoname should be indexed by default.

3 Likes

@Sachin_Mane these issues are best discussed over a pull request!

Game for it. Asking what’s the best place? Is after_migrate script okay?

Better to add the index automatically in the doctype.json

also for the modified property, add it as a part of the table creation query

@rmehta we can have after_save hook on doctype to set index on the autoname and modified columns. However this also needs to be changed for all existing doctypes.

why hook, just push it in the controller!

Yes for the name, its best to change the doctype .json properties too.

Okay. Done. Will send PR.

@rmehta It looks like changes for adding index on modified column are already pushed by @ManasSolanki in https://github.com/frappe/frappe/blob/develop/frappe/model/db_schema.py

Have sent pull request for creating index on autoname field -

2 Likes