This one trick can make MariaDB 30x faster!

Yes, it is a bit of a click-bait title, but in this case running one SQL command did improve a community user’s performance by that much. It could help you too.

The story

A community user posted in a couple of places that when migrating a large WordPress installation to MariaDB that they were seeing a certain query performing terribly. The query was taking 1.5 seconds to execute and it was clear from the explain plan that the optimizer was not making an ideal decision about the join order.

The query was generated by WordPress so it would not be easy to force an index, or rewrite the query.

The solution

I suggested the user run this command on all the tables involved:

ANALYZE TABLE tbl PERSISTENT FOR ALL;

Once the user had done this they were blown away by it. The query suddenly went down to a 0.05 second execution time.

Why this worked

The reason why things were slow was because the optimizer didn’t know anything about the data itself. It had to make assumptions on the spread of the data and unfortunately those assumptions were incorrect.

The “ANALYZE TABLE” command generates and stores histograms for the data. This allows the optimizer to better estimate the frequency of values in the index and therefore make better decisions about the join order and filtering.

To learn more about this you can watch the MariaDB Server Fest 2021 recording on the topic. There is also a Knowledge Base article describing how it helps and another describing the “ANALYZE TABLE” command usage.

Featured image used under CC BY 4.0, attribution: Speed Optimization Illustration on IconScout