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.