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

Full-Column Unique Index

    XMLWordPrintable

    Details

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

      Description

      From the MySQL Manual:

      A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

      Can you please implement a way to specify that uniqueness should extend beyond the prefix and apply to the entire column? Obviously the index could be used as a start, but any rows with matching prefix should be compared against the entire value.

      1) Search the index
      2) Compare the VARCHAR length of any matching prefix columns
      3) Compare the actual values

      Sure it would be a bit slower than index-only, but grant us the flexibility to decide full comparisons v.s. index and remove this limitation.

      Some practical examples:
      SOF - Best primary key for storing URLs
      SOF - Most efficient way to store URLs in Mysql?
      SE - MySQL: Storing unique URLs

      Thanks!

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              belugabehr BELUGABEHR
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: