pgv_dates

Beschreibung

Struktur

Attribut	Nullable?	Datentyp
d_day	Ja	int(11)
d_month	Ja	varchar(5)
d_mon	Ja	int(11)
d_year	Ja	int(11)
d_datestamp	Ja	int(11)
d_julianday1	Ja	int(11)
d_julianday2	Ja	int(11)
d_fact	Ja	varchar(15)
d_gid	Ja	varchar(20)
d_file	Ja	smallint(5) unsigned
d_type	Ja	varchar(13)

Indizes

Name	Typ	Attribute
pgv_date_day	Index	d_day
pgv_date_month	Index	d_month
pgv_date_mon	Index	d_mon
pgv_date_year	Index	d_year
pgv_date_datestamp	Index	d_datestamp
pgv_date_julianday1	Index	d_julianday1
pgv_date_julianday2	Index	d_julianday2
pgv_date_gid	Index	d_gid
pgv_date_file	Index	d_file
pgv_date_type	Index	d_type
pgv_date_fact_gid	Index	d_fact d_gid

Anmerkungen

Diese Tabelle hat kein PRIMARY Index, da einige Events zweimal zu einer Person oder Familie registiert werden können.

Ansonsten scheint mir die Tabelle mit unnötigen, nicht ausreichend selektivien Indexes überhäuft zu sein.

Langläufer


SELECT death.d_gid AS id, death.d_julianday2-birth.d_julianday1 AS age
 
  FROM pgv_dates AS death, pgv_dates AS birth, pgv_individuals AS indi
 
 WHERE indi.i_id=birth.d_gid 

   AND birth.d_gid=death.d_gid 

   AND death.d_file=1 

   AND birth.d_file=death.d_file 

   AND birth.d_file=indi.i_file 

   AND birth.d_fact IN ('BIRT', 'CHR', 'BAPM', '_BRTM') 

   AND death.d_fact IN ('DEAT', 'BURI', 'CREM') 

   AND birth.d_julianday1!=0 

   AND death.d_julianday1!=0 

   AND 1=1 

 ORDER BY age DESC LIMIT 0, 1

;

Diese Abfrage berechnet die älteste Person.