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 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
Try this feature out on Frappe Cloud
https://frappecloud.com/docs/performance-tuning
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