SQL Practical Guide

Search

SNOMED CT Document Library

    Loading...

Page tree

Versions Compared

Old Version 3

changes.mady.by.user Jon Zammit

Saved on

compared with

New Version 4

changes.mady.by.user David Markwell

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Additional indexes are required to support rapid access to interrelated data (for example the descriptions and relationships associated with an identified concept).  outlines  

Caption reference
CapRefIdadd-index
CapRefTypeTable
 outlines the rationale for each of the additional indexes used to improve performance of specific tables in the SNOMED CT example database

...

. Further indexes or revisions of these indexes may also be useful to further enhance performance.  is  

Caption reference
CapRefIdadd-index
CapRefTypeTable
 is also intended as a starting point for the developing SNOMED CT solutions in other database environments. However, the benefits of adding particular indexes will depend on the characteristics of the database server. Therefore, some of these indexes may not be required and other indexing strategies may be more effective at improving performance.

Caption label
CapIdadd-index
CapTypeTable
Additional Indexes for Specific Tables
Database TableIndex NameIndex ColumnsRationale for this Index
(full or snap)_descriptiondescription_conceptconceptIdFind descriptions for concept.
description_langconceptId,languageCodeFind descriptions with specific language code for concept.
description_term

term (fulltext)

Footnote Macro

Full text indexes for terms allow effective searching. However, unless the database is correctly configured, short words, abbreviations and stop words may prevent effective indexing of common clinical terms. For further details refer to A.7.1 Required MySQL Configuration Settings.

Search for terms.
(full or snap)_relationshiprelationship_destdestinationId,typeId,sourceIdFind concepts with relationships of a specified type of which a specified concept is the destinationId (value or supertype) or find relationships with a specific combination of destination, type and source.
relationship_sourcesourceId,typeId,destinationIdFind concepts with relationships of a specified type of which a specified concept is the destinationId (defined concept or subtype) or find relationships with a specific combination of source, type and destination.
(full or snap)_statedRelationshipstatedRelationship_destdestinationId,typeId,sourceIdFind concepts with stated relationships of a specified type of which a specified concept is the destinationId (value or supertype) or find relationships with a specific combination of destination, type and source.
statedRelationship_sourcesourceId,typeId,destinationIdFind concepts with relationships of a specified type of which a specified concept is the destinationId (defined concept or subtype) or find relationships with a specific combination of source, type and destination.
(full or snap)_textDefinitiontextDefinition_conceptconceptIdFind text definitions for concept.
textDefinition_langconceptId,languageCodeFind text definitions with specific language code for concept.
textDefinition_termterm (fulltext)Search for terms in text definitions.

(full or snap)_refset_[REFSETTYPE]

These indexes are applied to all refset tables

[REFSETTYPE]_creferencedComponentIdFind rows in any reference set of type [REFSETTYPE] that refer to a specified referenced component.
[REFSETTYPE]_rcrefsetId,referencedComponentIdFind rows in an identified reference set of type [REFSETTYPE] that refer to a specified referenced component.
(full or snap)_refset_ExtendedMapExtendedMap_maprefsetId,mapTarget Find map records in a specified mapping reference set for a particular mapTarget. Find all concepts that have a map to a particular mapTarget in a specified mapping reference set.
(full or snap)_refset_SimpleMapSimpleMap_maprefsetId,mapTarget Find map records in a specified mapping reference set for a particular mapTarget. Find all concepts that have a map to a particular mapTarget in a specified mapping reference set.
(full or snap)_refset_MRCMAttributeDomainMRCMAttributeDomain_domdomainIdFind attribute domain information for a specified domain.



...

Display Footnotes Macro