TectonicUnit missing common tree indexes #6736
Description
Describe the bug
TectonicUnit is missing BTREE Indexes on its Name and FullName columns. Since these columns are very frequently searched on, they should be indexed like the Name and FullName columns for other tree tables.
(In a similar vein, TectonicUnit -> GUID could arguably be indexed as well).
Currently (as of 63f46d0), only the primary and foreign keys are indexed on the TectonicUnit table.
Below is a list of all the indexes on the TectonicUnit table:
SHOW INDEXES FROM tectonicunit
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| tectonicunit | 0 | PRIMARY | 1 | tectonicunitid | A | 0 | NULL | NULL | BTREE | NO | |||
| tectonicunit | 1 | tectonicunit_AcceptedID_85b089fc_fk_tectonicunit_TectonicUnitID | 1 | AcceptedID | A | 0 | NULL | NULL | YES | BTREE | NO | ||
| tectonicunit | 1 | tectonicunit_CreatedByAgentID_1ba499d6_fk_agent_agentid | 1 | CreatedByAgentID | A | 0 | NULL | NULL | YES | BTREE | NO | ||
| tectonicunit | 1 | tectonicunit_ModifiedByAgentID_3fd34630_fk_agent_agentid | 1 | ModifiedByAgentID | A | 0 | NULL | NULL | YES | BTREE | NO | ||
| tectonicunit | 1 | tectonicunit_ParentID_fe77b3e4_fk_tectonicunit_TectonicUnitID | 1 | ParentID | A | 0 | NULL | NULL | YES | BTREE | NO | ||
| tectonicunit | 1 | tectonicunit_TectonicUnitTreeDefI_c7586c7e_fk_tectonicu | 1 | TectonicUnitTreeDefID | A | 0 | NULL | NULL | YES | BTREE | NO | ||
| tectonicunit | 1 | tectonicunit_TectonicUnitTreeDefI_c84d73ed_fk_tectonicu | 1 | TectonicUnitTreeDefItemID | A | 0 | NULL | NULL | YES | BTREE | NO |
In comparison with two other tree tables, Geography and Taxon (although all non-TectonicUnit tree tables have Name and FullName indexes):
SHOW INDEXES FROM geography
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| geography | 0 | PRIMARY | 1 | GeographyID | A | 1889 | NULL | NULL | BTREE | NO | |||
| geography | 1 | GeoNameIDX | 1 | Name | A | 1889 | NULL | NULL | YES | BTREE | NO | ||
| geography | 1 | GeoFullNameIDX | 1 | FullName | A | 1889 | NULL | NULL | YES | BTREE | NO | ||
| geography | 1 | FK496A777CBF9C9714 | 1 | GeographyTreeDefID | A | 1 | NULL | NULL | BTREE | NO | |||
| geography | 1 | FK496A777C7699B003 | 1 | CreatedByAgentID | A | 4 | NULL | NULL | YES | BTREE | NO | ||
| geography | 1 | FK496A777CE3C6E41A | 1 | GeographyTreeDefItemID | A | 5 | NULL | NULL | BTREE | NO | |||
| geography | 1 | FK496A777C83AAF47E | 1 | ParentID | A | 269 | NULL | NULL | YES | BTREE | NO | ||
| geography | 1 | FK496A777CF484C03B | 1 | AcceptedID | A | 1 | NULL | NULL | YES | BTREE | NO | ||
| geography | 1 | FK496A777C5327F942 | 1 | ModifiedByAgentID | A | 2 | NULL | NULL | YES | BTREE | NO |
SHOW INDEXES FROM taxon
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| taxon | 0 | PRIMARY | 1 | TaxonID | A | 16109 | NULL | NULL | BTREE | NO | |||
| taxon | 1 | TaxonCommonNameIDX | 1 | CommonName | A | 4027 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | TaxonGuidIDX | 1 | GUID | A | 16109 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | TaxonFullNameIDX | 1 | FullName | A | 16109 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | TaxonNameIDX | 1 | Name | A | 16109 | NULL | NULL | BTREE | NO | |||
| taxon | 1 | TaxonomicSerialNumberIDX | 1 | TaxonomicSerialNumber | A | 2 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | EnvironmentalProtectionStatusIDX | 1 | EnvironmentalProtectionStatus | A | 30 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | FK6908ECA70B1006E | 1 | HybridParent2ID | A | 1 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | FK6908ECA7699B003 | 1 | CreatedByAgentID | A | 3 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | FK6908ECABB9210FE | 1 | TaxonTreeDefItemID | A | 10 | NULL | NULL | BTREE | NO | |||
| taxon | 1 | FK6908ECA7BF1F70B | 1 | VisibilitySetByID | A | 1 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | FK6908ECABE9D724C | 1 | ParentID | A | 3221 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | FK6908ECA70B0FCAD | 1 | HybridParent1ID | A | 1 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | FK6908ECA2F773E09 | 1 | AcceptedID | A | 282 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | FK6908ECA5327F942 | 1 | ModifiedByAgentID | A | 4 | NULL | NULL | YES | BTREE | NO | ||
| taxon | 1 | FK6908ECAEFA9D5F8 | 1 | TaxonTreeDefID | A | 1 | NULL | NULL | BTREE | NO | |||
| taxon | 1 | FK6908ECA83339302 | 1 | TaxonAttributeID | A | 1 | NULL | NULL | YES | BTREE | NO |