MariaDB slow queries in your site

In the analytics tab of your site dashboard, you'll be able to see slow queries 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

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

On this page