[Impressum/Imprint] [Kontakt/Contact Me]
Home Working with TNG's SQL-based Reports (A Guide to my Report Library) How To Use My Report Definitions For Your TNG Website Some More SQL Tips & Tricks
 TNG - Tips & Tricks
 Working with TNG's SQL-based Reports (A Guide to my Report Library)

Adding Additional Indexes To Your TNG Database Tables

Please read first

For certain reports, it's possible to add additional indexes to your TNG database tables. This makes execution faster, especially if you have a large database. On the other hand, more indexes needing more disk space.


Complete List of Possible Additional Indexes

Don't add all these indexes! Check which indexes you really need (because of slow query execution), add only these really needed indexes.

(*) The short name is used in the report-specific documentation for referencing each index definition!

Table Name Index Definition Short Name (*)
tng_associations ALTER TABLE tng_associations ADD INDEX local_rela(relationship); tng_associations.local_rela
tng_branches ALTER TABLE tng_branches ADD INDEX local_gedcom(gedcom); tng_branches.local_gedcom
tng_branchlinks ALTER TABLE tng_branchlinks ADD INDEX local_persfamID(persfamID); tng_branchlinks.local_persfamID
  ALTER TABLE tng_branchlinks ADD INDEX local_branch(branch); tng_branchlinks.local_branch
  ALTER TABLE tng_branchlinks ADD INDEX local_gedcom(gedcom); tng_branchlinks.local_gedcom
tng_cemeteries ALTER TABLE tng_cemeteries ADD INDEX local_cemname5(cemname, city, county, state, country); tng_cemeteries.local_cemname5
tng_children ALTER TABLE tng_children ADD INDEX local_familyID(familyID); tng_children.local_familyID
  ALTER TABLE tng_children ADD INDEY local_haskids(haskids); tng_children.local_haskids
tng_citations ALTER TABLE tng_citations ADD INDEX local_gedpers(gedcom, persfamID); (**) tng_citations.local_gedpers
  ALTER TABLE tng_citations ADD INDEX local_sourceID(sourceID); (**) tng_citations.local_sourceID
  ALTER TABLE tng_citations ADD INDEX local_eventID(eventID); tng_citations.local_eventID
tng_events ALTER TABLE tng_events ADD INDEX local_eventtypeID(eventtypeID); (**) tng_events.local_eventtypeID
  ALTER TABLE tng_events ADD INDEX local_eventdatetr(eventdatetr); tng_events.local_eventdatetr
  ALTER TABLE tng_events ADD INDEX local_eventplace(eventplace(255)); tng_events.local_eventplace
  ALTER TABLE tng_events ADD INDEX local_addressID(addressID); tng_events.local_addressID
  ALTER TABLE tng_events ADD INDEX local_parenttag(parenttag); (**) tng_events.local_parenttag
tng_eventtypes ALTER TABLE tng_eventtypes ADD INDEX local_tag(tag); tng_eventtypes.local_tag
  ALTER TABLE tng_eventtypes ADD INDEX local_tag_descr(tag, description); tng_eventtypes.local_tag_descr
tng_families ALTER TABLE tng_families ADD INDEX local_living(living); tng_families.local_living
  ALTER TABLE tng_families ADD INDEX local_marrdatetr(marrdatetr); (**) tng_families.local_marrdatetr
  ALTER TABLE tng_families ADD INDEX local_marrplace(marrplace(248)); tng_families.local_marrplace
  ALTER TABLE tng_families ADD INDEX local_marrplace(divplace(248)); tng_families.local_divplace
  ALTER TABLE tng_families ADD INDEX local_wife(wife); tng_families.local_wife
  ALTER TABLE tng_families ADD INDEX local_husband(husband); tng_families.local_husband
tng_media ALTER TABLE tng_media ADD INDEX local_alwayson(alwayson); tng_media.local_alwayson
  ALTER TABLE tng_media ADD INDEX local_cemeteryID(cemeteryID); tng_media.local_cemeteryID
  ALTER TABLE tng_media ADD INDEX local_lat(latitude); tng_media.local_lat
  ALTER TABLE tng_media ADD INDEX local_long(longitude); tng_media.local_long
tng_medialinks ALTER TABLE tng_medialinks ADD INDEX local_mediaID_simple(mediaID); tng_medialinks.local_mediaID_simple
tng_notelinks ALTER TABLE tng_notelinks ADD INDEX local_gedpers(gedcom, persfamID); tng_notelinks.local_gedpers
  ALTER TABLE tng_notelinks ADD INDEX local_xnoteID(xnoteID); tng_notelinks.local_xnoteID
  ALTER TABLE tng_notelinks ADD INDEX local_eventID(eventID); tng_notelinks.local_eventID
tng_people ALTER TABLE tng_people ADD INDEX local_altbirthdatetr(altbirthdatetr); (**) tng_people.local_altbirthdatetr
  ALTER TABLE tng_people ADD INDEX local_altbirthplace(altbirthplace(248)); (***) tng_people.local_altbirthplace
  ALTER TABLE tng_people ADD INDEX local_birthdatetr(birthdatetr); (**) tng_people.local_birthdatetr
  ALTER TABLE tng_people ADD INDEX local_birthplace(birthplace(248)); (***) tng_people.local_birthplace
  ALTER TABLE tng_people ADD INDEX local_burialdatetr(burialdatetr); (**) tng_people.local_burialdatetr
  ALTER TABLE tng_people ADD INDEX local_burialplace(burialplace(248)); (***) tng_people.local_burialplace
  ALTER TABLE tng_people ADD INDEX local_deathdatetr(deathdatetr); (**) tng_people.local_deathdatetr
  ALTER TABLE tng_people ADD INDEX local_deathplace(deathplace(248)); (***) tng_people.local_deathplace
  ALTER TABLE tng_people ADD INDEX local_personID(personID); tng_people.local_personID
  ALTER TABLE tng_people ADD INDEX local_living(living); tng_people.local_living
  ALTER TABLE tng_people ADD INDEX local_branch(branch); tng_people.local_branch
  ALTER TABLE tng_people ADD INDEX local_sex(sex(1)); tng_people.local_sex
tng_places ALTER TABLE tng_places ADD INDEX local_place(place); tng_places.local_place
  ALTER TABLE tng_places ADD INDEX local_lat(latitude); tng_places.local_lat
  ALTER TABLE tng_places ADD INDEX local_long(longitude); tng_places.local_long
tng_repositories ALTER TABLE tng_repositories ADD INDEX local_addressID(addressID); tng_repositories.local_addressID
tng_sources ALTER TABLE tng_sources ADD INDEX local_repoID(repoID); (**) tng_sources.local_repoID
  ALTER TABLE tng_sources ADD INDEX local_shorttitle(shorttitle(30)); tng_sources.local_shorttitle
tng_xnotes ALTER TABLE tng_xnotes ADD INDEX local_ID(ID, gedcom); tng_xnotes.local_ID

(**) = these are the most common additional indexes
(***) = these additional indexes are not necessary after TNG 6.1.1 - becauses these indexes are now part of all TNG installations. If you have add the marked indexes earlier, you should remove them after updating to TNG 6.1.1


Copyright © http://www.ahnendaten.de/tng/
Letzte Aktualisierung am 11. Juni 2017

Home Working with TNG's SQL-based Reports (A Guide to my Report Library) How To Use My Report Definitions For Your TNG Website Some More SQL Tips & Tricks