Functional Indexes #7224

New Issue

admin · 2026-01-22T15:47:21+01:00

admin commented

2026-01-22 15:47:21 +01:00

Originally created by @SerheyDolgushev on GitHub (Sep 15, 2023).

Feature Request

MySQL 8.0.13 and higher supports functional indexes. Similar functionality seems to be available in PostgreSQL for a while. But it is not supported in Doctrine yet. Is it a good idea to implement it?

Q	A
New Feature	yes
RFC	yes
BC Break	no

Summary

Let's assume we have the following Article entity:

<?php

namespace App\Entity;

use App\Repository\ArticleRepository;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ArticleRepository::class)]
class Article
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $title = null;

    #[ORM\Column]
    private ?\DateTimeImmutable $publishedAt = null;

    // getters and setters
}

And the following migration will be generated for it:

CREATE TABLE article (
    id INT AUTO_INCREMENT NOT NULL,
    title VARCHAR(255) NOT NULL,
    published_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    PRIMARY KEY(id)
);

Let's add some records:

INSERT INTO article (title, published_at)
VALUES
	('Article 1', '2023-09-09 13:17:17'),
	('Article 2', '2023-09-09 13:23:17'),
	('Article 3', '2023-09-10 12:32:22'),
	('Article 4', '2023-09-12 15:31:12'),
	('Article 5', '2023-09-13 09:02:21')
;

Register WEEKDAY DQL string function, and try to find all the articles published on Sunday. You will get SQL query similar to:

SELECT id, title FROM article WHERE WEEKDAY(published_at) = 6;

And run EXPLAIN for it:

EXPLAIN SELECT id, title FROM article WHERE WEEKDAY(published_at) = 6\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

There is a very easy way to improve it by adding a functional index:

CREATE INDEX published_at_week_day_idx ON article ((WEEKDAY(published_at)));

And the new execution plan is:

EXPLAIN SELECT id, title FROM article WHERE WEEKDAY(published_at) = 6\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: published_at_week_day_idx
          key: published_at_week_day_idx
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

The current problems are:

It is impossible to add such an index using ORM\Index attribute:
```
 #[ORM\Index(name: 'published_day_of_week_idx', columns: ['expression:WEEKDAY(published_at)'])]
```
leads to There is no column with name "expression:WEEKDAY(published_at)" on table "article". error on php bin/console make:migration.

Even if the index is created using the SQL, php bin/console doctrine:migrations:diff returns the following error:

Doctrine\DBAL\Schema\Index::_addColumn(): Argument #1 ($column) must be of type string, null given, called in XXX/vendor/doctrine/dbal/src/Schema/Index.php on line 72

and it happens because the function index has no value for Column_name, but it has Expression:

HOW indexes FROM article WHERE Key_name = 'published_at_week_day_idx'\G
*************************** 1. row ***************************
        Table: article
   Non_unique: 1
     Key_name: published_at_week_day_idx
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: weekday(`published_at`)
1 row in set (0.01 sec)

So the main question is does it worth implementing function indexes at all?

Originally created by @SerheyDolgushev on GitHub (Sep 15, 2023). ### Feature Request MySQL 8.0.13 and higher supports [functional indexes](https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts). Similar functionality seems to be [available in PostgreSQL for a while](https://www.postgresql.org/docs/current/indexes-expressional.html). But it is not supported in Doctrine yet. Is it a good idea to implement it? | Q | A |------------ | ------ | New Feature | yes | RFC | yes | BC Break | no #### Summary Let's assume we have the following Article entity: ```php <?php namespace App\Entity; use App\Repository\ArticleRepository; use Doctrine\ORM\Mapping as ORM; #[ORM\Entity(repositoryClass: ArticleRepository::class)] class Article { #[ORM\Id] #[ORM\GeneratedValue] #[ORM\Column] private ?int $id = null; #[ORM\Column(length: 255)] private ?string $title = null; #[ORM\Column] private ?\DateTimeImmutable $publishedAt = null; // getters and setters } ``` And the following migration will be generated for it: ```SQL CREATE TABLE article ( id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) NOT NULL, published_at DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)', PRIMARY KEY(id) ); ``` Let's add some records: ```SQL INSERT INTO article (title, published_at) VALUES ('Article 1', '2023-09-09 13:17:17'), ('Article 2', '2023-09-09 13:23:17'), ('Article 3', '2023-09-10 12:32:22'), ('Article 4', '2023-09-12 15:31:12'), ('Article 5', '2023-09-13 09:02:21') ; ``` Register `WEEKDAY` DQL string function, and try to find all the articles published on Sunday. You will get SQL query similar to: ```SQL SELECT id, title FROM article WHERE WEEKDAY(published_at) = 6; ``` And run `EXPLAIN` for it: ```SQL EXPLAIN SELECT id, title FROM article WHERE WEEKDAY(published_at) = 6\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: article partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.01 sec) ``` There is a very easy way to improve it by adding a functional index: ```SQL CREATE INDEX published_at_week_day_idx ON article ((WEEKDAY(published_at))); ``` And the new execution plan is: ```SQL EXPLAIN SELECT id, title FROM article WHERE WEEKDAY(published_at) = 6\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: article partitions: NULL type: ref possible_keys: published_at_week_day_idx key: published_at_week_day_idx key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.01 sec) ``` The current problems are: 1. It is impossible to add such an index using `ORM\Index` attribute: ```php #[ORM\Index(name: 'published_day_of_week_idx', columns: ['expression:WEEKDAY(published_at)'])] ``` leads to `There is no column with name "expression:WEEKDAY(published_at)" on table "article".` error on `php bin/console make:migration`. 2. Even if the index is created using the SQL, `php bin/console doctrine:migrations:diff` returns the following error: ```bash Doctrine\DBAL\Schema\Index::_addColumn(): Argument #1 ($column) must be of type string, null given, called in XXX/vendor/doctrine/dbal/src/Schema/Index.php on line 72 ``` and it happens because the function index has no value for `Column_name`, but it has `Expression`: ```SQL HOW indexes FROM article WHERE Key_name = 'published_at_week_day_idx'\G *************************** 1. row *************************** Table: article Non_unique: 1 Key_name: published_at_week_day_idx Seq_in_index: 1 Column_name: NULL Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: weekday(`published_at`) 1 row in set (0.01 sec) ``` So the main question is does it worth implementing function indexes at all?

admin commented

2026-01-22 15:47:22 +01:00

@drmax24 commented on GitHub (Nov 15, 2024):

Any workaround guys? A way to suppress it or smth?

@drmax24 commented on GitHub (Nov 15, 2024): Any workaround guys? A way to suppress it or smth?

admin commented

2026-01-22 15:47:23 +01:00

@katzendrama commented on GitHub (Jun 10, 2025):

Couldn´t you use a custom column definition for this and even increase compatibility to older DBs like this?

    #[ORM\Column(
        type: 'string',
        insertable: false,
        updatable: false,
        columnDefinition: "VARCHAR(255) GENERATED ALWAYS AS (concat(firstName, ' ', name) stored NOT NULL",
        generated: 'ALWAYS',
    )]
    private string $fullName;

I think this could be an option for you...

@katzendrama commented on GitHub (Jun 10, 2025): Couldn´t you use a custom column definition for this and even increase compatibility to older DBs like this? ``` #[ORM\Column( type: 'string', insertable: false, updatable: false, columnDefinition: "VARCHAR(255) GENERATED ALWAYS AS (concat(firstName, ' ', name) stored NOT NULL", generated: 'ALWAYS', )] private string $fullName; ``` I think this could be an option for you...

admin referenced this issue

2026-01-22 16:07:21 +01:00

[PR #7224] Remove obsolete backslashes from underscores #10384

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#7224