Add missing field indexes

This includes https://github.com/specify/specify7/issues/6736

We can dramatically improve query and WorkBench performance if we add indexes to fields that are commonly searched.  

This list was largely produced by taking every column whose name contained `name`, `guid`, `uniqueIdentifier`, `identifier`, `number` and similar), cross-referencing each one with the table’s declared indexes in the XML data model, and then manually modifying the results with matching fields sourced solely from `datamodel.json`, filtered to drop fields ending in numeric suffixes. I then removed a few dozen fields that are unlikely to be searched commonly. This is still open to discussion, so please give your feedback if any.

Some ideas:

- **agentidentifier (edu.ku.brc.specify.datamodel.AgentIdentifier)** (2 fields)
  - identifier (column: Identifier, type: String)
  - identifierType (column: IdentifierType, type: String)
- **agentspecialty (edu.ku.brc.specify.datamodel.AgentSpecialty)** (2 fields)
  - orderNumber (column: OrderNumber, type: Integer)
  - specialtyName (column: SpecialtyName, type: String)
- **agentvariant (edu.ku.brc.specify.datamodel.AgentVariant)** (1 fields)
  - name (column: Name, type: String)
- **attachment (edu.ku.brc.specify.datamodel.Attachment)** (1 fields)
  - origFilename (column: OrigFilename, type: text)
- **attachmentdataset (edu.ku.brc.specify.datamodel.Spattachmentdataset)** (1 fields)
  - name (column: Name, type: String)
- **attachmentmetadata (edu.ku.brc.specify.datamodel.AttachmentMetadata)** (1 fields)
  - name (column: Name, type: String)
- **author (edu.ku.brc.specify.datamodel.Author)** (1 fields)
  - orderNumber (column: OrderNumber, type: Short)
- **collectionobject (edu.ku.brc.specify.datamodel.CollectionObject)** (2 fields)
  - name (column: Name, type: String)
  - projectNumber (column: ProjectNumber, type: String)
- **collectionobjectgroup (edu.ku.brc.specify.datamodel.CollectionObjectGroup)** (2 fields)
  - guid (column: GUID, type: String)
  - name (column: Name, type: String)
- **collectionobjectgrouptype (edu.ku.brc.specify.datamodel.CollectionObjectGroupType)** (1 fields)
  - name (column: Name, type: String)
- **collectionobjectproperty (edu.ku.brc.specify.datamodel.CollectionObjectProperty)** (1 fields)
  - guid (column: GUID, type: String)
- **collectionobjecttype (edu.ku.brc.specify.datamodel.CollectionObjectType)** (1 fields)
  - name (column: Name, type: String)
- **collectionreltype (edu.ku.brc.specify.datamodel.CollectionRelType)** (1 fields)
  - name (column: Name, type: String)
- **exchangein (edu.ku.brc.specify.datamodel.ExchangeIn)** (1 fields)
  - exchangeInNumber (column: ExchangeInNumber, type: String)
- **exsiccataitem (edu.ku.brc.specify.datamodel.ExsiccataItem)** (1 fields)
  - number (column: Number, type: String)
- **geography (edu.ku.brc.specify.datamodel.Geography)** (4 fields)
  - commonName (column: CommonName, type: String)
  - guid (column: GUID, type: String)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - nodeNumber (column: NodeNumber, type: Integer)
- **geographytreedef (edu.ku.brc.specify.datamodel.GeographyTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **geographytreedefitem (edu.ku.brc.specify.datamodel.GeographyTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **geologictimeperiod (edu.ku.brc.specify.datamodel.GeologicTimePeriod)** (2 fields)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - nodeNumber (column: NodeNumber, type: Integer)
- **geologictimeperiodtreedef (edu.ku.brc.specify.datamodel.GeologicTimePeriodTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **geologictimeperiodtreedefitem (edu.ku.brc.specify.datamodel.GeologicTimePeriodTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **institutionnetwork (edu.ku.brc.specify.datamodel.InstitutionNetwork)** (1 fields)
  - altName (column: AltName, type: String)
- **latlonpolygon (edu.ku.brc.specify.datamodel.LatLonPolygon)** (1 fields)
  - name (column: Name, type: String)
- **lithostrat (edu.ku.brc.specify.datamodel.LithoStrat)** (2 fields)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - nodeNumber (column: NodeNumber, type: Integer)
- **lithostrattreedef (edu.ku.brc.specify.datamodel.LithoStratTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **lithostrattreedefitem (edu.ku.brc.specify.datamodel.LithoStratTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **locality (edu.ku.brc.specify.datamodel.Locality)** (2 fields)
  - guid (column: GUID, type: String)
- **localityupdaterowresult (edu.ku.brc.specify.datamodel.LocalityUpdateRowResult)** (1 fields)
  - rownumber (column: rownumber, type: Integer)
- **materialsample (edu.ku.brc.specify.datamodel.MaterialSample)** (1 fields)
  - guid (column: GUID, type: String)
- **morphbankview (edu.ku.brc.specify.datamodel.MorphBankView)** (1 fields)
  - viewName (column: ViewName, type: String)
- **otheridentifier (edu.ku.brc.specify.datamodel.OtherIdentifier)** (1 fields)
  - identifier (column: Identifier, type: String)
- **picklist (edu.ku.brc.specify.datamodel.PickList)** (3 fields)
  - fieldName (column: FieldName, type: String)
  - filterFieldName (column: FilterFieldName, type: String)
  - tableName (column: TableName, type: String)
- **preparationproperty (edu.ku.brc.specify.datamodel.PreparationProperty)** (1 fields)
  - guid (column: GUID, type: String)
- **preptype (edu.ku.brc.specify.datamodel.PrepType)** (1 fields)
  - name (column: Name, type: String)
- **referencework (edu.ku.brc.specify.datamodel.ReferenceWork)** (1 fields)
  - libraryNumber (column: LibraryNumber, type: String)
- **relativeage (edu.ku.brc.specify.datamodel.RelativeAge)** (1 fields)
  - verbatimName (column: VerbatimName, type: text)
- **spauditlogfield (edu.ku.brc.specify.datamodel.SpAuditLogField)** (1 fields)
  - fieldName (column: FieldName, type: String)
- **spdataset (edu.ku.brc.specify.datamodel.Spdataset)** (1 fields)
  - name (column: Name, type: String)
- **specifyuser (edu.ku.brc.specify.datamodel.SpecifyUser)** (1 fields)
  - name (column: Name, type: String)
- **spexportschema (edu.ku.brc.specify.datamodel.SpExportSchema)** (1 fields)
  - schemaName (column: SchemaName, type: String)
- **spexportschemaitem (edu.ku.brc.specify.datamodel.SpExportSchemaItem)** (1 fields)
  - fieldName (column: FieldName, type: String)
- **spexportschemaitemmapping (edu.ku.brc.specify.datamodel.SpExportSchemaItemMapping)** (1 fields)
  - exportedFieldName (column: ExportedFieldName, type: String)
- **spexportschemamapping (edu.ku.brc.specify.datamodel.SpExportSchemaMapping)** (1 fields)
  - mappingName (column: MappingName, type: String)
- **spfieldvaluedefault (edu.ku.brc.specify.datamodel.SpFieldValueDefault)** (2 fields)
  - fieldName (column: FieldName, type: String)
  - tableName (column: TableName, type: String)
- **splibraryrole (edu.ku.brc.specify.datamodel.LibraryRole)** (1 fields)
  - name (column: Name, type: String)
- **splocalecontainer (edu.ku.brc.specify.datamodel.SpLocaleContainer)** (1 fields)
  - pickListName (column: PickListName, type: String)
- **splocalecontaineritem (edu.ku.brc.specify.datamodel.SpLocaleContainerItem)** (2 fields)
  - pickListName (column: PickListName, type: String)
  - webLinkName (column: WebLinkName, type: String)
- **spmerging (edu.ku.brc.specify.datamodel.Spmerging)** (1 fields)
  - name (column: Name, type: String)
- **spquery (edu.ku.brc.specify.datamodel.SpQuery)** (1 fields)
  - contextName (column: ContextName, type: String)
- **spqueryfield (edu.ku.brc.specify.datamodel.SpQueryField)** (2 fields)
  - fieldName (column: FieldName, type: String)
  - formatName (column: FormatName, type: String)
- **sprole (edu.ku.brc.specify.datamodel.Role)** (1 fields)
  - name (column: Name, type: String)
- **spviewsetobj (edu.ku.brc.specify.datamodel.SpViewSetObj)** (1 fields)
  - fileName (column: FileName, type: String)
- **storage (edu.ku.brc.specify.datamodel.Storage)** (2 fields)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - nodeNumber (column: NodeNumber, type: Integer)
- **storagetreedef (edu.ku.brc.specify.datamodel.StorageTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **storagetreedefitem (edu.ku.brc.specify.datamodel.StorageTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **taxon (edu.ku.brc.specify.datamodel.Taxon)** (4 fields)
  - cultivarName (column: CultivarName, type: String)
  - groupNumber (column: GroupNumber, type: String)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - nodeNumber (column: NodeNumber, type: Integer)
- **taxontreedef (edu.ku.brc.specify.datamodel.TaxonTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **taxontreedefitem (edu.ku.brc.specify.datamodel.TaxonTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **tectonicunit (edu.ku.brc.specify.datamodel.TectonicUnit)** (5 fields)
  - fullName (column: FullName, type: String)
  - guid (column: GUID, type: String)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - name (column: Name, type: String)
  - nodeNumber (column: NodeNumber, type: Integer)
- **tectonicunittreedef (edu.ku.brc.specify.datamodel.TectonicUnitTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **tectonicunittreedefitem (edu.ku.brc.specify.datamodel.TectonicUnitTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **voucherrelationship (edu.ku.brc.specify.datamodel.VoucherRelationship)** (1 fields)
  - voucherNumber (column: VoucherNumber, type: String)
  - 
- **agentidentifier (edu.ku.brc.specify.datamodel.AgentIdentifier)** (2 fields)
  - identifier (column: Identifier, type: String)
  - identifierType (column: IdentifierType, type: String)
- **agentspecialty (edu.ku.brc.specify.datamodel.AgentSpecialty)** (2 fields)
  - orderNumber (column: OrderNumber, type: Integer)
  - specialtyName (column: SpecialtyName, type: String)
- **agentvariant (edu.ku.brc.specify.datamodel.AgentVariant)** (1 fields)
  - name (column: Name, type: String)
- **attachment (edu.ku.brc.specify.datamodel.Attachment)** (1 fields)
  - origFilename (column: OrigFilename, type: text)
- **attachmentdataset (edu.ku.brc.specify.datamodel.Spattachmentdataset)** (1 fields)
  - name (column: Name, type: String)
- **attachmentmetadata (edu.ku.brc.specify.datamodel.AttachmentMetadata)** (1 fields)
  - name (column: Name, type: String)
- **author (edu.ku.brc.specify.datamodel.Author)** (1 fields)
  - orderNumber (column: OrderNumber, type: Short)
- **collectionobject (edu.ku.brc.specify.datamodel.CollectionObject)** (2 fields)
  - name (column: Name, type: String)
  - projectNumber (column: ProjectNumber, type: String)
- **collectionobjectgroup (edu.ku.brc.specify.datamodel.CollectionObjectGroup)** (2 fields)
  - guid (column: GUID, type: String)
  - name (column: Name, type: String)
- **collectionobjectgrouptype (edu.ku.brc.specify.datamodel.CollectionObjectGroupType)** (1 fields)
  - name (column: Name, type: String)
- **collectionobjectproperty (edu.ku.brc.specify.datamodel.CollectionObjectProperty)** (1 fields)
  - guid (column: GUID, type: String)
- **collectionobjecttype (edu.ku.brc.specify.datamodel.CollectionObjectType)** (1 fields)
  - name (column: Name, type: String)
- **collectionreltype (edu.ku.brc.specify.datamodel.CollectionRelType)** (1 fields)
  - name (column: Name, type: String)
- **exchangein (edu.ku.brc.specify.datamodel.ExchangeIn)** (1 fields)
  - exchangeInNumber (column: ExchangeInNumber, type: String)
- **exsiccataitem (edu.ku.brc.specify.datamodel.ExsiccataItem)** (1 fields)
  - number (column: Number, type: String)
- **geography (edu.ku.brc.specify.datamodel.Geography)** (4 fields)
  - commonName (column: CommonName, type: String)
  - guid (column: GUID, type: String)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - nodeNumber (column: NodeNumber, type: Integer)
- **geographytreedef (edu.ku.brc.specify.datamodel.GeographyTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **geographytreedefitem (edu.ku.brc.specify.datamodel.GeographyTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **geologictimeperiod (edu.ku.brc.specify.datamodel.GeologicTimePeriod)** (2 fields)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - nodeNumber (column: NodeNumber, type: Integer)
- **geologictimeperiodtreedef (edu.ku.brc.specify.datamodel.GeologicTimePeriodTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **geologictimeperiodtreedefitem (edu.ku.brc.specify.datamodel.GeologicTimePeriodTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **institutionnetwork (edu.ku.brc.specify.datamodel.InstitutionNetwork)** (1 fields)
  - altName (column: AltName, type: String)
- **latlonpolygon (edu.ku.brc.specify.datamodel.LatLonPolygon)** (1 fields)
  - name (column: Name, type: String)
- **lithostrat (edu.ku.brc.specify.datamodel.LithoStrat)** (2 fields)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - nodeNumber (column: NodeNumber, type: Integer)
- **lithostrattreedef (edu.ku.brc.specify.datamodel.LithoStratTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **lithostrattreedefitem (edu.ku.brc.specify.datamodel.LithoStratTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **locality (edu.ku.brc.specify.datamodel.Locality)** (2 fields)
  - guid (column: GUID, type: String)
- **localityupdaterowresult (edu.ku.brc.specify.datamodel.LocalityUpdateRowResult)** (1 fields)
  - rownumber (column: rownumber, type: Integer)
- **materialsample (edu.ku.brc.specify.datamodel.MaterialSample)** (1 fields)
  - guid (column: GUID, type: String)
- **morphbankview (edu.ku.brc.specify.datamodel.MorphBankView)** (1 fields)
  - viewName (column: ViewName, type: String)
- **otheridentifier (edu.ku.brc.specify.datamodel.OtherIdentifier)** (1 fields)
  - identifier (column: Identifier, type: String)
- **picklist (edu.ku.brc.specify.datamodel.PickList)** (3 fields)
  - fieldName (column: FieldName, type: String)
  - filterFieldName (column: FilterFieldName, type: String)
  - tableName (column: TableName, type: String)
- **preparationproperty (edu.ku.brc.specify.datamodel.PreparationProperty)** (1 fields)
  - guid (column: GUID, type: String)
- **preptype (edu.ku.brc.specify.datamodel.PrepType)** (1 fields)
  - name (column: Name, type: String)
- **referencework (edu.ku.brc.specify.datamodel.ReferenceWork)** (1 fields)
  - libraryNumber (column: LibraryNumber, type: String)
- **relativeage (edu.ku.brc.specify.datamodel.RelativeAge)** (1 fields)
  - verbatimName (column: VerbatimName, type: text)
- **spauditlogfield (edu.ku.brc.specify.datamodel.SpAuditLogField)** (1 fields)
  - fieldName (column: FieldName, type: String)
- **spdataset (edu.ku.brc.specify.datamodel.Spdataset)** (1 fields)
  - name (column: Name, type: String)
- **specifyuser (edu.ku.brc.specify.datamodel.SpecifyUser)** (1 fields)
  - name (column: Name, type: String)
- **spexportschema (edu.ku.brc.specify.datamodel.SpExportSchema)** (1 fields)
  - schemaName (column: SchemaName, type: String)
- **spexportschemaitem (edu.ku.brc.specify.datamodel.SpExportSchemaItem)** (1 fields)
  - fieldName (column: FieldName, type: String)
- **spexportschemaitemmapping (edu.ku.brc.specify.datamodel.SpExportSchemaItemMapping)** (1 fields)
  - exportedFieldName (column: ExportedFieldName, type: String)
- **spexportschemamapping (edu.ku.brc.specify.datamodel.SpExportSchemaMapping)** (1 fields)
  - mappingName (column: MappingName, type: String)
- **spfieldvaluedefault (edu.ku.brc.specify.datamodel.SpFieldValueDefault)** (2 fields)
  - fieldName (column: FieldName, type: String)
  - tableName (column: TableName, type: String)
- **splibraryrole (edu.ku.brc.specify.datamodel.LibraryRole)** (1 fields)
  - name (column: Name, type: String)
- **splocalecontainer (edu.ku.brc.specify.datamodel.SpLocaleContainer)** (1 fields)
  - pickListName (column: PickListName, type: String)
- **splocalecontaineritem (edu.ku.brc.specify.datamodel.SpLocaleContainerItem)** (2 fields)
  - pickListName (column: PickListName, type: String)
  - webLinkName (column: WebLinkName, type: String)
- **spmerging (edu.ku.brc.specify.datamodel.Spmerging)** (1 fields)
  - name (column: Name, type: String)
- **sppermission (edu.ku.brc.specify.datamodel.SpPermission)** (1 fields)
  - name (column: Name, type: String)
- **spprincipal (edu.ku.brc.specify.datamodel.SpPrincipal)** (1 fields)
  - name (column: Name, type: String)
- **spquery (edu.ku.brc.specify.datamodel.SpQuery)** (1 fields)
  - contextName (column: ContextName, type: String)
- **spqueryfield (edu.ku.brc.specify.datamodel.SpQueryField)** (2 fields)
  - fieldName (column: FieldName, type: String)
  - formatName (column: FormatName, type: String)
- **sprole (edu.ku.brc.specify.datamodel.Role)** (1 fields)
  - name (column: Name, type: String)
- **spviewsetobj (edu.ku.brc.specify.datamodel.SpViewSetObj)** (1 fields)
  - fileName (column: FileName, type: String)
- **storage (edu.ku.brc.specify.datamodel.Storage)** (2 fields)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - nodeNumber (column: NodeNumber, type: Integer)
- **storagetreedef (edu.ku.brc.specify.datamodel.StorageTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **storagetreedefitem (edu.ku.brc.specify.datamodel.StorageTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **taxon (edu.ku.brc.specify.datamodel.Taxon)** (4 fields)
  - cultivarName (column: CultivarName, type: String)
  - groupNumber (column: GroupNumber, type: String)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - nodeNumber (column: NodeNumber, type: Integer)
- **taxontreedef (edu.ku.brc.specify.datamodel.TaxonTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **taxontreedefitem (edu.ku.brc.specify.datamodel.TaxonTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **tectonicunit (edu.ku.brc.specify.datamodel.TectonicUnit)** (5 fields)
  - fullName (column: FullName, type: String)
  - guid (column: GUID, type: String)
  - highestChildNodeNumber (column: HighestChildNodeNumber, type: Integer)
  - name (column: Name, type: String)
  - nodeNumber (column: NodeNumber, type: Integer)
- **tectonicunittreedef (edu.ku.brc.specify.datamodel.TectonicUnitTreeDef)** (1 fields)
  - name (column: Name, type: String)
- **tectonicunittreedefitem (edu.ku.brc.specify.datamodel.TectonicUnitTreeDefItem)** (1 fields)
  - name (column: Name, type: String)
- **voucherrelationship (edu.ku.brc.specify.datamodel.VoucherRelationship)** (1 fields)
  - voucherNumber (column: VoucherNumber, type: String)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Add missing field indexes #7482

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Add missing field indexes #7482

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions