Working with hundreds of different customers I often face similar problems around running queries. One very common problem when trying to optimize a database environment is index usage. A query that cannot use an index is usually a long-running one, consuming more memory or triggering more disk iops.
A very common case is when a query uses a filter condition against a column that is involved in some kind of functional expression. An index on that column can not be used.
Starting from MySQL 8.0.13 functional indexes are supported. In this article, I’m going to show what they are and how they work.
The Well-Known Problem
As already mentioned, a very common problem about index usage is when you have a filter condition against one or more columns involved in some kind of functional expression.
Let’s see a simple example.
You have a table called products containing the details of your products, …[Read more]