Bad data design leads to poor performances: from 105 minutes to 17 seconds

I requested permission to share anonymized queries and execution plans from this case, but the client declined. So, I’ll be providing only high-level explanations and general figures in this story.

About two years ago, I had the privilege of working with a company in Northern Europe that was struggling to aggregate data from several recently acquired subsidiaries, all operating in the same industry. The core purpose of the system in question was to present a unified webui, allowing customers to track their orders and receive alerts when they were ready for pickup. The technical challenge stemmed from their data structure. They had a legacy table from their original system and a new table populated with event-sourced data (yes, I know…) from the newly integrated subsidiaries. Both tables shared a similar structure and core columns. However, because order states were computed and refreshed from a message queue, things got tricky. Also customers could place an order at one subsidiary but choose to pick it up at another. This meant the system had to reconcile state and data changes across both tables to maintain consistency and accuracy.

Given the sheer volume of data (yeah, well… event sourcing, right?) in both tables, the query — even with proper indexing — took around 1 hour and 45 minutes to execute. As a result, customers could only be alerted about order availability roughly every two hours. The client understandably wanted to tighten the update window, ideally around 15 minutes. To achieve this, they explored external tools and even considered rewriting some parts of the data pipeline. But both solutions were costly. After summarizing their options and kinda hitting a wall, they reached out to MariaDB Corporation — which led to me stepping in to provide a limited-time TAM (Technical Account Manager) service.

We discussed the issue in depth — the background, how things had evolved to this point, and the solution they were envisioning going forward. Eventually, I got to the point of reviewing the actual query and the table structures themselves. And that’s when I stumbled upon a (very) low-hanging fruit: one of the join conditions between the two large tables involved date columns being wrapped in functions — on both sides :

CAST(date_column1 AS CHAR) = STR_TO_DATE(date_column2,'%x%c%d')

As you probably (and should) know, wrapping columns in functions during joins or filters prevents indexes from being used. In this case, the functions were purely for data type manipulation — casting a DATE and an INTEGER into CHAR just to make them comparable. A quick inspection of the data types involved confirmed the OG issue:

TABLE1
(
...
date_column1 INT COMMENT 'YYYYMMDD',
...);

TABLE2
(
...
date_column2 DATE,
...);

The client was already pleased that we’d identified a potential performance gain within just a few hours on day one. Now, they were eager to see how much it could actually speed things up. So, how do we fix this — and more importantly, can we do it with minimal disruption ?

Long story short: yes, we can, so let’s see how.

Since modifying the loading software was off the table, that also meant we couldn’t touch the existing columns. Thankfully, MariaDB provides a very handy feature for situations like this: generated columns. A generated column is a virtual or stored column whose value is automatically calculated based on an expression involving other columns in the same table. The expression can use built-in functions or UDFs (user-defined functions) (remember that time when i displayed how powerful UDFs can be).

So the real question became: Do we create a generated column in one of the tables? Or in both?

Since we’re aiming for performance, and INTEGER is the fastest data type, we’ll lean into that. Let’s generate a clean, indexed integer value to join on — and keep everything else as it is.

ALTER TABLE TABLE2 ADD date_column2_as_int INT AS CAST(DATE_FORMAT(date_column2, '%Y%m%d') AS UNSIGNED) VIRTUAL COMMENT 'YYYYMMDD - for index+join purposes';

Now that we have our integer column available on both sides, we can update the join condition in the query to use them directly, as follows:

date_column1 = date_column2_as_int

Also, to maximize the performance benefits of the new generated column, let’s create an index that mimic the existing one, but replace the plain date column with the generated column:

CREATE INDEX idx_match ON TABLE2( ..., date_column2_as_int);

After setting up a test environment with the latest production backup, the results were stunning: according to the explain plan, the new indexes were used, and the query now runs in 17 seconds.

**17 SECONDS — HOLY ***!!!

I knew it would be faster by a solid margin, but wow… just wow.

To wrap it up, this perfectly illustrates the two key lessons I always share with my performance tuning trainees. First, databases are like buildings — none is stronger/faster than its foundations. Second, data design is the foundation of any database, so invest time and brainpower into it. And remember: data design is mostly database engine–independent.

In today’s case, we relied on MariaDB-specific features like generated columns to emulate good data design, because rebuilding from scratch wasn’t an option. But in an ideal world, both tables would have had aligned design, so data types in our case, from the start — and the performance would have been there from the get-go.

Now tell me, who would not like to benefit from such feats and knowledge ? Reach out to me and le’ts see what you need and how i can help you solve those pain points of yours !!