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.