Top 5 Advanced MySQL Features, Part 5

Virtual Columns

Values of a generated (virtual) column are computed from an expression included in the column definition.

Hey there fellow database enthusiasts! Have you ever wished you could calculate values when storing data to your MySQL tables? Well, look no further because today we’re diving into the exciting world of MySQL virtual columns!

Benefits & usage:

Consider a table named “products” that stores information about products in an e-commerce store. This table has the following columns:

• product_id (primary key)
• name
• quantity
• unit_price

We can create a virtual column named “total_price” that calculates the total price for each product by multiplying its quantity and unit price, as follows:

ALTER TABLE products
ADD COLUMN total_price AS (quantity * unit_price);

When selecting data from the “products” table, we will have access to the calculated “total_price” field without having to store it physically in the table. This can help improve query performance, as we don’t have to repeat the calculation every time we access the data.

Or let’s consider a table named “employees” that stores information about employees in a company. This table has the following columns:

• employee_id (primary key)
• first_name
• last_name
• hire_date

We can create a virtual column named “years_employed” that calculates the number of years an employee has been with the company, as follows:

ALTER TABLE employees
ADD COLUMN years_employed AS (YEAR(CURDATE()) - YEAR(hire_date));

Fuzzy Searching

Now that we’re getting warmed up, how about implementing virtual columns in the pursuit of fuzzy searching?

Consider a table named “customers” that stores information about customers in a retail store. This table has the following columns:

• customer_id (primary key)
• first_name
• last_name

We can create virtual columns that help implement a fuzzy search based on the customer’s name, as follows:

ALTER TABLE customers
ADD COLUMN search_name AS (REGEXP_REPLACE(CONCAT(first_name, ' ', last_name), '[^a-zA-Z0-9]+', ''));

Now, we can use the virtual column “search_name” to perform a fuzzy search based on the customer’s full name, while also removing any special characters. For example, if we search the name “Tim O’reilly or Tim Oreilly”, we’ll get the appropriate result (by disregarding the apostrophe). Now we’re simplifying our search logic by avoiding having to write complex string manipulations or multiple LIKE clauses in our queries.

Something to consider:

While virtual columns can be a huge benefit, there are some considerations:

• Performance: Can impact query performance, especially for complex expressions or for large tables. Queries that involve virtual columns may require additional processing to evaluate the expression for each row, which can slow down the query.
• Storage: They do not physically store data, they only store the expression used to calculate the value. This means that the expression must be re-evaluated every time the virtual column is used in a query.
• Limited indexes: They can be indexed, but the type of index that can be used is limited. For example, virtual columns can be indexed with a B-Tree index, but not with a full-text index.
• Maintenance: Must be updated whenever the underlying data changes, which can require additional maintenance. If the expression used to calculate the virtual column is complex, it can be more difficult to maintain.
• Compatibility: Not supported by all database systems, and their implementation may differ between systems. This can impact portability and compatibility with other databases.

Considering these limitations, virtual columns should be used with caution and only when they provide a significant benefit in terms of functionality or performance. It may be necessary to periodically evaluate the impact of virtual columns on the database and make changes as needed to ensure optimal performance and stability.