Add indexes to your database

From TNG_Wiki
Jump to navigation Jump to search

My database was slowing down very fast and I had no idea why, I upgraded the whole server and still no luck. For three weeks I have tried all kind of things when I finally remembered that Ulrich Kretschmer had an article about adding indexes to speed up searches.

This is an adaptation of his article about it. You can find his article here. Adding indexes can take up more disk space but I found that the additional disk space is negligible. One of the TNG users now can make a gedcom of 30.000 people in about 3 minutes. I have a database with almost half a million people and it takes me now half an hour to create a gedcom.

Copy and paste the text between the lines into the MySQL section of your MySQL admin, IMPORTANT: Make a backup before you try anything and use this at your own risk! Also read the article of Ulrich first, I just wanted to make it easier to create all the indexes at once. Another option is to copy and paste this into Notepad and delete the ones you don't need, also don't forget to adjust the names of the tables if you use different ones. Finally copy and paste what is left into the mysql admin and then click go.

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