The System begins to slow down when the DB has huge number of transactions records(Stock ledger, stock entry,Delivery Note etc). We see from the Slow Query Log that many of the searches are done based on the unindexed fields.
Below are few examples
- against_sales_order, against_sales_invoice in delivery note item table
- quality_inspection in stock entry detail table
- reference_doctype, reference_docname in workflow action table
Indexing these fields will improve the select query performance.
However will it impact the Performance negatively Insert or Update Action ?
Any pointers on the fields which are indexed in the the current deployments will be really helpful.
Any best practices to be done for indexing will also be really helpful.