[MySQL] Query op temporary tables

[roland-d]

Goedendag allen,

Onlangs ben ik aan de gang gegaan met temporary tables in MySQL. Dit in het kader van het optimaliseren van een bestaande query voor een genealogie programma waar ik mee bezig ben.

Doel
Families isoleren die aan niemand gekoppeld zijn.

Probleem stelling

Het programma heeft een tabel genaam families en ziet er zo uit:

code:

f_id | varchar(255)
f_file | int(11)
f_husb | varchar(255)
f_wife | varchar(255)
f_chil | text 
f_gedcom | text
f_numchil | int(11)

f_husb is het ID van de man in de vorm van I10, f_wife is het ID van de vrouw in de vorm van I10, f_chil is het ID van het kind/kinderen in de vorm van I10;I11; en f_file is het ID van de gebruikte stamboom.

De volgende query heb ik gemaakt:

code:

CREATE TEMPORARY TABLE IF NOT EXISTS temp_fama 
SELECT f_id AS family, f_husb AS member, 'H' AS type
FROM gm_families WHERE f_file='3' AND f_husb != '' 
UNION 
SELECT f_id AS family, f_wife AS member, 'W' AS type 
FROM gm_families 
WHERE f_file='3' and f_wife != '' 
UNION 
SELECT f_id AS family, 
SUBSTRING_INDEX( f_chil, ';', 1 ) AS member, 
'C' AS type 
FROM gm_families 
WHERE f_file='3' and f_chil != '' AND SUBSTRING_INDEX( f_chil, ';', 1 ) != '' 
UNION 
SELECT f_id AS family, 
SUBSTRING_INDEX(SUBSTRING_INDEX( f_chil, ';', 2 ) , ';' , -1 ) AS member, 
'C' AS type 
FROM gm_families 
WHERE f_file='3' 
AND f_chil != '' 
AND SUBSTRING_INDEX(SUBSTRING_INDEX( f_chil, ';', 2 ) , ';' , -1 ) != ''

Dit is de query als een familie 2 kinderen heeft. Mochten er meer kinderen zijn dan wordt de query aangevuld door de PHP code met:

code:

UNION 
SELECT f_id AS family, 
SUBSTRING_INDEX(SUBSTRING_INDEX( f_chil, ';', 2 ) , ';' , -1 ) AS member, 
'C' AS type 
FROM gm_families 
WHERE f_file='3' 
AND f_chil != '' 
AND SUBSTRING_INDEX(SUBSTRING_INDEX( f_chil, ';', 2 ) , ';' , -1 ) != ''

Het getal 2 wordt dan opgehood met 1, dit net zoveel unions als er kinderen zijn. De query wordt tweemaal uitgevoerd om 2 tijdelijke tabellen te krijgen, te weten temp_fama en temp_famb. Nu ik mijn 2 tabellen heb, wil ik weten welke personen in temp_fama eenmaal voorkomen in temp_famb. Dit om families te isoleren die aan niemand gelinkt zijn. Hier loop ik vast
De volgende query heb ik geprobeerd:

code:

SELECT temp_fama.*, temp_famb.* 
FROM temp_fama, temp_famb 
WHERE (
SELECT b.* 
FROM temp_famb b 
WHERE b.member=temp_fama.member AND b.family != temp_fama.family) LIMIT 3

Hier krijg ik dan de error dat er een fout in mijn query zit in de buurt van de 1e WHERE. Hoe ik ook schommel met de haakjes, de melding blijft hetzelfde. Deze query op gewone tabellen werkt wel.

Vraag
Wat ik dus wil weten is welke personen in temp_fama eenmaal voorkomen in temp_famb. Waar ga ik fout in mijn bovenstaande query

Hartelijk dank.

RolandD

[NMe]

Wat doe je rare dingen? Comma separated values opslaan in een tabel gaat voorbij aan het hele idee van normalisering, en als ik jou was zou ik eens opzoeken wat dat was. Als je dat namelijk doet zul je zien dat je gewoon normale ID's op kan slaan voor husband/wife (een veld wat gewoon samengevoegd kan worden tot één veld, als je in een ander veld het geslacht van de partner opslaat). Verder is het veel handiger om de kinderen in een koppeltabel op te nemen, waarin je een eigen ID opslaat (autonummering), het ID van het betreffende kind en de ID's van beide ouders.

Lees in elk geval dit even door.

[Creepy]

Het feit dat je een vel;d hebt waarin je nog eens met een ; zaken gescheiden moet houden wil al zeggen dat je database nog niet helemaal optimaal is. Hierdoor wordt het maken van queries ook onnodig moeilijker.

Als je een aparte tabel maakt voor de kinderen en dat weer koppelt m.b.f. f_id dan kan je met een simpele join al je UNIONS voorkomen.

Als je wilt weten welke personen maar 1 keer voorkomen (en niet meer) in een tabel dan kan je gebruik maken van count en having. Vervolgens kan je met een inner join alleen de mensen selecteren die in de andere tabel staan.

Zoek ook eens op "normaliseren"

edi:t: * Creepy mept -NMe- again.

[ Voor 3% gewijzigd door Creepy op 22-04-2006 13:04 ]

[Cyphax]

Ik zou de database eerst maar eens normaliseren. Dat niemand anders dat nog voorgesteld heeft.

Maar serieus, de tijd die je nog nodig hebt om dit werkend te krijgen heb je zo terugverdiend. Dus echt even een gigantische stap terug doen, want al krijg je dit werkend, dan zit je volgende week weer met je handen in het haar, maar dan nog veel erger...
Dit is trouwens ook niet heel goed voor je database performance volgens mij.

[roland-d]

Allemaal bedankt voor de reacties. Het normaliseren van de database daar wordt inderdaad aangewerkt. Tot hoever je de normalisatie doorvoert is even de vraag. Er kan in 1 GEDCOM record erg veel informatie zitten. Nu is het niet zo moeilijk omdat op te slaan maar als je dan een pagina aanroept van bijvoorbeeld 10 personen met gegevens heb je tig queries nodig om per persoon de gegevens op te halen. Momenteel is de code zo, dat het record ook in een tekstveld word opgeslagen. Met de PHP code wordt dan de benodigde info eruit gehaald. Aangezien het bestaande code is, zal er nog wel ruimte voor verbetering zijn Vandaar dat ik ermee bezig ben. Er is naast de family tabel ook nog een individual tabel, waar gegevens van de individuen staan opgeslagen.

2-NMe-
Wel een goed idee om dan bij die individuen de vader en moeder op te slaan. Dan is dat gelijk mijn koppeltabel.

Ook voor mijzelf is het construeren van geneste queries nog een hele hordenloop Oefening baart kunst.

Bedankt voor het nieuwe inzicht. Ik ga ermee aan de slag

[roland-d]

Nou, we zijn weer een eind op weg. Ik heb de database aangepast en ziet er nu als volgt uit:

code:

gm_families (alle families)
Field   Type    Null    Default
f_id    varchar(255)    Yes     NULL
f_file  int(11)     Yes     NULL
f_husb  varchar(255)    Yes     NULL
f_wife  varchar(255)    Yes     NULL

gm_individual_child (geeft aan welke personen als kind in een gezin zitten)
Field   Type    Null    Default
ID  int(11)     No  
file    int(11)     No  0
pid     varchar(255)    No  
family_id   varchar(255)    Yes     NULL

gm_individual_spouse (geeft aan welke personen als man/vrouw in een gezin zitten)
Field   Type    Null    Default
ID  int(11)     No  
file    int(11)     No  0
pid     varchar(255)    No  
family_id   varchar(255)    Yes     NULL

gm_individuals (alle individuen)    
Field   Type    Null    Default
i_id    varchar(255)    Yes     NULL
i_file  int(11)     Yes     NULL
i_name  varchar(255)    Yes     NULL
i_surname   varchar(100)    Yes     NULL
i_gender    varchar(255)    Yes     NULL

Nu is het doel nog steeds om alle families te zoeken die alleenstaand zijn. Oftewel alle leden van het gezin linken alleen naar dat ene gezin.

De volgende query had ik verzonnen:

code:

SELECT DISTINCT f_id, f_husb, f_wife
FROM gm_families
LEFT JOIN gm_individual_child ON f_id = gm_individual_child.family_id
LEFT JOIN gm_individual_spouse ON f_id = gm_individual_spouse.family_id
WHERE f_file = 2
AND gm_individual_child.file = 2
AND gm_individual_spouse.file = 2
ORDER BY f_id
LIMIT 0,3

Zonder de limit hangt mn PC De 3 rijen doen er een 21 seconde over. Hier leid ik dus uit af dat er iets niet goed is met de query.

Deze query om te kijken welke personen nergens aan gelinkt zijn doet het wel goed:

code:

SELECT i_id 
FROM gm_individuals 
LEFT JOIN gm_individual_spouse ON gm_individual_spouse.pid=gm_individuals.i_id 
LEFT JOIN gm_individual_child ON gm_individual_child.pid=gm_individuals.i_id 
WHERE gm_individual_spouse.pid IS NULL 
AND gm_individual_child.pid IS NULL 
AND i_file = '3' 
ORDER BY i_name

Kan iemand mij op de goede weg helpen?

Hartelijk dank.

[Verwijderd]

Een paar indexjes op de verschillende ID's die je gebruikt zou een stuk moeten schelen...
Overigens laar MySQL wel erg brakke SQL toe:

code:

SELECT DISTINCT f_id, f_husb, f_wife

is doodgewoon foute SQL. Wanneer je DISTINCT of GROUP BY gebruikt, moeten de overige waarden die je ophaalt geagregeerd zijn. Bijvoorbeeld:

code:

SELECT DISTINCT f_id, min(f_husb), min(f_wife)

Al denk ik dat je die DISTINCT helemaal niet nodig hebt, want ik neem aan dat f_id de primary key is? Maar dan is een PK van type varchar(255) weer niet handig, omdat dat zo beroerd indexeert...

[Sybr_E-N]

varchar als id gebruiken? waar ben je mee bezig?? NULL waarden wil je over het algemeen ook niet in je database hebben. Gebruik je wel Primary key's en indexen?

[NMe]

Verwijderd schreef op maandag 01 mei 2006 @ 13:30:
Een paar indexjes op de verschillende ID's die je gebruikt zou een stuk moeten schelen...
Overigens laar MySQL wel erg brakke SQL toe:

code:

SELECT DISTINCT f_id, f_husb, f_wife

is doodgewoon foute SQL. Wanneer je DISTINCT of GROUP BY gebruikt, moeten de overige waarden die je ophaalt geagregeerd zijn.

Ow? Dat is nieuw voor me. Leg eens uit?

[Verwijderd]

Oeps...
Ik nam aan dat 'ie 1 f_id met de daarbij behorende f_husb en f_wife wilde hebben.
Als 'ie op zoek wil naar meerdere f_id / f_husb / f_wife combinaties, dan mag 't inderdaad op deze manier. Maar polygamie is toch nog steeds niet toegestaan in Nederland?

Maar ja, z'n datamodel is ook al niet homo-huwelijk vriendelijk, dus misschien moet er wat aan bijgespijkerd worden.