since I'm fairly new to the concept of generated columns, I have a question about their use. I understand that it is supposed to help speed up queries performed on the table in cases when the db optimizer is unable to use table indexes due to a function used directly on the index (e.g. DATE(order_date), order_date is indexed).
In an attempt to speed up a query on my mariadb 10.5.16 db table, I have added a generated column:
ALTER TABLE orders ADD COLUMN month_order_date TINYINT GENERATED ALWAYS AS (month(order_date)) VIRTUAL;
and a couple of corresponding indexes like this one:
ALTER TABLE orders ADD INDEX generated_index_all (month_order_date,class,type);
It worked great, speeding up the query almost 10-fold. My question is this tho - is it enough to create said virtual column and any needed indexes once, or is it necessary to do it every once in a while?