MariaDB slow queries in your site

Slow queries take up lots of CPU on your database server. Ideally there should be none.

In the analytics tab of your site dashboard, you'll be able to see slow queries (tracked by MariaDB) being run on your site database. These usually mean that the queries can be optimized to improve performance (and reduce usage!)

Prerequisites: You need to be on a private bench and on Version 14 or higher for the following

Adding database indexes

Version 15 users can also follow the doc here for easier experience: https://frappeframework.com/docs/user/en/profiling#query-optimization-using-recorder

Version 14 users can also try out this feature out on Frappe Cloud

One of the easier ways to speed up queries is to add indexes to the filtered columns. Usually, adding composite indexes rather than single column indexes is a good idea. MariaDB has an article explaining the same.

You can see existing indexes on your site with:

SHOW INDEXES FROM `tab<doctype_name>`;

after going to mariadb console with:

bench --site <site_name> mariadb

In frappe (since v14), it's easy to index columns with the bench add-database-index command. Simply find the columns that need indexing and run

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

This index creates a Property Setter document as well, so the changes persist across migrations and restores.

Note: Adding too many indexes will slow down insertion of records into the table. If you run into any problems, you can remove indexes added by deleting them from the Property Setter doctype and running bench migrate on your site