CODEWITHSUNDEEP
mariadbdatabase-indexes
Oralover
Oralover

Reputation: 93

MariaDB can we create a functional index ( index based on a function )

can i create a functional index in MariaDB like in Oracle we can:

create index fnc_idx on table_name (to_char(date_col, 'MM-YYYY'));

or any alternate solution...? regards

Upvotes: 1

Views: 834

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You can use calculated columns as approach:

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` datetime NOT NULL,
  -- add calculated column
  `order_month` VARCHAR(6) AS (CONCAT(MONTH(order_date), YEAR(order_date))) PERSISTENT,
  `client_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  -- add calculated column index
  INDEX (`order_month`)
);

MariaDB fiddle

Upvotes: 1

Related Questions