Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-14372

make option_value multi-column keys unique?

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 4.4.4
    • Fix Version/s: Unscheduled
    • Component/s: None
    • Labels:
      None
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)

      Description

      I'm wondering about the tradeoff between performance and database consistency that was made with the following two keys in civicrm_option_value table:

      KEY `index_option_group_id_value` (`value`(128),`option_group_id`),
      KEY `index_option_group_id_name` (`name`(128),`option_group_id`),

      Note that one field in each is longer than the indexed portion:

      `value` varchar(512) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The actual value stored (as a foreign key) in the data record. Functions which need lookup option_value.title should use civicrm_option_value.option_group_id plus civicrm_option_value.value as the key.',
      `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Stores a fixed (non-translated) name for this option value. Lookup functions should use the name as the key for the option value row.',

      It seems to me it would be a good idea for the indexes to use the whole string in both cases and have their uniqueness guaranteed for the common use cases of short name and value strings.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              joemurray Joe Murray
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: