Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17993

WHERE F(X) = constY should used F(X) generated index and query WHERE G=constY

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      None

      Description

      I noticed when porting MDEV-15854 that in MySQL:

      https://github.com/mysql/mysql-server/blob/8.0/mysql-test/r/func_uuid.result#L297-L301

      There wasn't any specific UUID code causing this so it could be generic.

      For any pure function, a generated index on F(X) for a query `WHERE F(X) = Y` could used the generated index `G=F(X)`to search for `Y``.

      There's an overhead of course searching for a generated column`F(X)` in a query, however this only needs to be done if the comparison point is a constant and the other side is a function. When this occurs no index is usable anyway.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration