Analyze and Optimize Slow Database Queries

Slow database queries can significantly increase CPU usage and affect your site’s performance. Frappe Cloud provides tools to help you identify slow and frequently executed queries so you can optimize them and improve overall database efficiency.

View Slow Queries

MariaDB automatically tracks slow-running queries. You can review them from your site’s dashboard to identify queries that consume excessive CPU time.

1. Open your Site in the Frappe Cloud dashboard.
2. Navigate to the Analytics tab.
3. Review the list of Slow Queries to identify queries that need optimization.

Analyze Frequently Executed Queries

Some queries may execute quickly but run so frequently that they still create unnecessary CPU load.

1. Open your site’s Database Analyzer tab.
2. Review frequently executed queries.
3. Identify opportunities to optimize queries, reports, or custom code.

Optimize Queries with Database Indexes

Adding indexes to frequently filtered columns can significantly improve query performance. In many cases, composite indexes (indexes on multiple columns) perform better than single-column indexes.

View Existing Indexes

SHOW INDEXES FROM `tab<doctype_name>`;

Run the command from the MariaDB console:

bench --site <site_name> mariadb

Add a Composite Index

bench --site <site_name> add-database-index --doctype "<doctype_name>" --column <column_1> --column <column_2>

Example:

bench --site <site_name> add-database-index --doctype "Sales Order" --column order_type --column status

When an index is created using this command, Frappe also creates a Property Setter so the index persists across future migrations and restores.

Avoid Using COALESCE on Indexed Columns

Functions such as COALESCE() can prevent MariaDB from using indexes efficiently, resulting in slower queries.

• If you’re using the Frappe ORM, set ignore_ifnull=True in your get_list() query to avoid generating unnecessary COALESCE() statements.
• If you’re optimizing reports, consider creating a custom SQL Query Report that removes unnecessary COALESCE() functions.