Speed Up Your MariaDB Queries: Calculated Columns and Denormalization to the Rescue

Functional indexes allow you to index data based on the results of a function applied to a column or even multiple columns. This can be useful for speeding up queries that involve functions on the data.

While MariaDB doesn't have direct functional indexes, there are alternative approaches to achieve similar results depending on your specific scenario. These might involve:

• Calculated Columns: You can create a persistent calculated column that stores the function's result. Then, you can create a regular index on this new column. This can work well but adds some overhead for data storage and maintenance.
• Denormalization: If certain data is queried frequently with a specific function, you might consider denormalizing your tables by pre-computing and storing the function's results in a separate column. This can improve query speed but requires keeping your denormalized data synchronized.

Calculated Columns:

This example creates a table with a name column and a new calculated column named "full_name_upper" that stores the uppercase version of the name:

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  full_name_upper VARCHAR(255) AS (UPPER(name)) PERSISTENT
);

-- Now create an index on the full_name_upper column
CREATE INDEX full_name_upper_idx ON users(full_name_upper);

Denormalization:

This example assumes you have a table "products" with a "price" column and frequently query for products with a discounted price (price * 0.9). Here, we add a "discounted_price" column to store the pre-calculated discounted value:

ALTER TABLE products ADD COLUMN discounted_price DECIMAL(10,2) AS (price * 0.9) AFTER price;

-- Now create an index on the discounted_price column for faster discounted product searches
CREATE INDEX discounted_price_idx ON products(discounted_price);

1. Partial Indexes:

   • Create partial indexes on specific ranges or conditions within a column. This can be helpful if you only query a subset of the data based on a certain criteria.

   For example, if you have a "creation_date" column and primarily search for records created within the last month, you could create a partial index:

   CREATE TABLE my_table (
     id INT PRIMARY KEY,
     creation_date DATETIME NOT NULL,
     ...
   );
   
   CREATE INDEX recent_data_idx ON my_table(creation_date) WHERE creation_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
   

2. Materialized Views:

   • Materialized views are pre-computed copies of queries stored as database tables. They can significantly improve query performance if the underlying data and the view definition remain relatively stable.

   However, materialized views require additional maintenance to keep them synchronized with the base tables.

3. Triggers:

   • Triggers are database objects that automatically execute specific actions (like updates) when certain events occur in a table (like inserts or updates).

   While not a direct replacement, triggers can be used to pre-compute and store values based on functions in a separate column, similar to denormalization, but with automatic updates.

The best approach depends on your specific needs and data access patterns. Here's a quick comparison to help you decide: