Generated Columns and ProxySQL Instead of Referenced Tables

Generated ColumnsIn this post, we’ll look at how to improve queries using generated columns and ProxySQL instead of implementing a referenced table.

Developers and architects don’t always have the time or complete information to properly analyze and design a database. That is why we see tables with more fields than needed, or with incorrect types. The best solution is implementing a change in the database schema and/or application level. In this post, we’ll look at an example of generated columns (using a char field) instead of creating a referenced table, and how using generated columns and ProxySQL avoids changes at the application level.

For this example, I will be using the film table of the Sakila database (with some changes). The original film table had a language_id as tinyint, which refers to the language table:

I simplified the design of the table and added the language field as a char(20), as it is in table language:

We inserted 1M records and executed a query that filters by language:

The explain plan shows that it is performing a full table scan. Adding an index over language speeds up the query from 0.92 sec to 0.09 sec:

And the explain plan shows that it is using the language index:

Now, we are going to add a generated column and an index over this new field:

Finally, we are going to add a rule in ProxySQL with the replace pattern:

Now, when we execute the query through ProxySQL, it will replace:

With:

The explain plan shows it is using the language_id index:

And that the query time decreases to 0.06 sec: