[mysql] id ophalen

[Meijuh]

Hoi, ik zit met het volgende probleem.

Ik heb:

code:

A(id, blaat)
B(id, A_id)
C(id, A_id)
Foreign key B(A_id) references A(id)
Foreign key C(A_id) references A(id)

Nu wil ik alle id's van tabel A ophalen die niet in B.A_id of C.A_id zitten, maar ik zie even niet hoe.

De volgende query is vrij traag:

code:

Select a.id FROM A a 
WHERE a.blaat = $eenvariabele
AND (
a.id NOT IN(SELECT B.A_id WHERE a.id = B.A_id)
OR a.id NOT IN(SELECT C.A_id WHERE a.id = C.A_id)
)

Ik snap waarom dit langszaam is, maar ik zie niet hoe ik het beter kan doen?

[Verwijderd]

De query die je geeft werkt doet volgens mij meer "werk" dan er nodig is. Je weet namelijk al dat A_id uit tabel B en C in tabel A aanwezig zijn (Foreign key met reference?).

Je kan dan dus a.id selecteren waar a.blaat = $eenvariabele en a.id niet in B.A_id en niet in C.A_id zit.

code:

SELECT a.id FROM A a
WHERE a.blaat = $eenvariabele
AND a.id NOT IN (SELECT B.A_id FROM B)
AND a.id NOT IN (SELECT C.A_id FROM C)

[frickY]

Probeer het eens met een Outer Join (join B & join C) en een Having clause (HAVING B IS NULL AND C IS NULL).

[ Voor 30% gewijzigd door frickY op 10-01-2010 15:56 ]

[Meijuh]

frickY schreef op zondag 10 januari 2010 @ 15:55:
Probeer het eens met een Outer Join (join B & join C) en een Having clause (HAVING B IS NULL AND C IS NULL).

Ik heb het nu inderdaad met een outer join gedaan en voor het gemak heb ik de query opgesplitst in twee queries.
bedankt.

[mocean]

Meijuh schreef op zondag 10 januari 2010 @ 15:34:
...

code:

A(id, blaat)
B(id, A_id)
C(id, A_id)
Foreign key B(A_id) references A(id)
Foreign key C(A_id) references A(id)

Nu wil ik alle id's van tabel A ophalen die niet in B.A_id of C.A_id zitten, maar ik zie even niet hoe.

Volgens mij kan het makkelijk in één query:

SQL:

SELECT A.* 
FROM A
LEFT JOIN B ON B.A_id=A.id
LEFT JOIN C ON C.A_id=A.id
WHERE ISNULL(B.id) AND ISNULL(C.id)

[Verwijderd]

De oplossing die ik plaatste deed het ook in één query. Waarom zou je hier een JOIN toepassen en dan checken op NULL?

@Meijuh: ik ben wel benieuwd hoe je het dan hebt opgelost met twee queries?

[mocean]

Verwijderd schreef op zondag 10 januari 2010 @ 18:32:
De oplossing die ik plaatste deed het ook in één query. Waarom zou je hier een JOIN toepassen en dan checken op NULL?

@Meijuh: ik ben wel benieuwd hoe je het dan hebt opgelost met twee queries?

Met "AND a.id NOT IN (SELECT ...)" doe je volgens mij wel drie volledige selects en dat wordt vrij traag. Met een join op indices zoals in mijn oplossing is het een stukje sneller, maar functioneel doet het hetzelfde.

Ik zou ook wel willen weten wat de TS dan doet met 2 losse query's, want is totaal niet nodig (tenzij de werkelijke logica wat ingewikkelder is dan het gegeven voorbeeld).

[Meijuh]

Verwijderd schreef op zondag 10 januari 2010 @ 18:32:
De oplossing die ik plaatste deed het ook in één query. Waarom zou je hier een JOIN toepassen en dan checken op NULL?

Zoals de post boven mij. De join is inderdaad veel sneller.

@Meijuh: ik ben wel benieuwd hoe je het dan hebt opgelost met twee queries?

Ja dat was ook een beetje vaag hoe ik dat deed, het kan ook prima in 1 query, dus dat heb ik ook maar gedaan:)

[Verwijderd]

mocean schreef op zondag 10 januari 2010 @ 18:50:
[...]

Met "AND a.id NOT IN (SELECT ...)" doe je volgens mij wel drie volledige selects en dat wordt vrij traag. Met een join op indices zoals in mijn oplossing is het een stukje sneller, maar functioneel doet het hetzelfde.

Heb het even getest (@MySQL), de JOIN was inderdaad iets sneller (0.0004 vs 0.0005 op mijn "willekeurige" dataset met SQL_NO_CACHE hint). Als je de query profiler gebruikt dan is wel redelijk duidelijk te zien dat de JOIN versie minder verschillende taken afhandelt.

Er lijkt zelfs nog een klein performance verschilletje te zitten tussen WHERE ISNULL(B.A_id) en WHERE B.A_id IS NULL.

Al met al lijkt het me heel sterk dat de ene versie "veel sneller" is dan de andere.

[mocean]

Verwijderd schreef op zondag 10 januari 2010 @ 19:45:
[...]


Heb het even getest (@MySQL), de JOIN was inderdaad iets sneller (0.0004 vs 0.0005 op mijn "willekeurige" dataset met SQL_NO_CACHE hint). Als je de query profiler gebruikt dan is wel redelijk duidelijk te zien dat de JOIN versie minder verschillende taken afhandelt.

Er lijkt zelfs nog een klein performance verschilletje te zitten tussen WHERE ISNULL(B.A_id) en WHERE B.A_id IS NULL.

Al met al lijkt het me heel sterk dat de ene versie "veel sneller" is dan de andere.

En als je een testset maakt van zeg een miljoen per records per tabel? Ik vermoed dat dan de Join wel heel wat sneller zal worden dan de IN (SELECT ...) constructie.

[ACM]

Een andere variant met sub-queries is om gebruik te maken van exists, ipv de nu half-slachtige mix van exists en 'in' in de topicstart (een correcte variant met 'in' is al gegeven).

SQL:

SELECT a.id FROM a 
WHERE a.blaat = $eenvariabele
AND NOT EXISTS (SELECT * FROM b WHERE a.id = b.A_id)
AND NOT EXISTS (SELECT * FROM c WHERE a.id = c.A_id)

Maar als het query-technisch handig en mogelijk is, zal inderdaad doorgaans de join-variant in MySQL (iets) sneller zijn.

[Verwijderd]

De EXISTS methode is zelfs nog iets netter geloof ik. (Google eens op IN vs EXISTS.)

@mocean: heb het eens geprobeerd met een testset met een miljoen records voor de A tabel en een willekeurig gevulde B en C tabel met 100000 records. Verassend genoeg kwam na 5 tests de gemiddelde executie tijd voor de JOIN-query op 0.0011 en de "NOT IN"-query op 0.0010.

[mocean]

Verwijderd schreef op zondag 10 januari 2010 @ 22:21:
De EXISTS methode is zelfs nog iets netter geloof ik. (Google eens op IN vs EXISTS.)

@mocean: heb het eens geprobeerd met een testset met een miljoen records voor de A tabel en een willekeurig gevulde B en C tabel met 100000 records. Verassend genoeg kwam na 5 tests de gemiddelde executie tijd voor de JOIN-query op 0.0011 en de "NOT IN"-query op 0.0010.

Dat vind ik ook wel verrassend ja, staan de indices dan ook goed? Snap dan niet zo goed waarom die IN query eigenlijk sneller kan zijn. Maar goed, weer wat geleerd!

[Verwijderd]

Ik vond het ook verassend.

Dit is de structuur, zo klopt het geloof ik? (De data zal ik jullie besparen. )

SQL:

CREATE TABLE IF NOT EXISTS `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `blaat` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1010003 ;

CREATE TABLE IF NOT EXISTS `B` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `A_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`A_id`),
  KEY `A_id` (`A_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=100001 ;

CREATE TABLE IF NOT EXISTS `C` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `A_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`A_id`),
  KEY `A_id` (`A_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=100001 ;

ALTER TABLE `B`
  ADD CONSTRAINT `B_ibfk_1` FOREIGN KEY (`A_id`) REFERENCES `A` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `C`
  ADD CONSTRAINT `C_ibfk_1` FOREIGN KEY (`A_id`) REFERENCES `A` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

[Meijuh]

Verwijderd schreef op maandag 11 januari 2010 @ 12:31:
Ik vond het ook verassend.

Dit is de structuur, zo klopt het geloof ik? (De data zal ik jullie besparen. )

SQL:

CREATE TABLE IF NOT EXISTS `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `blaat` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1010003 ;

CREATE TABLE IF NOT EXISTS `B` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `A_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`A_id`),
  KEY `A_id` (`A_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=100001 ;

CREATE TABLE IF NOT EXISTS `C` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `A_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`A_id`),
  KEY `A_id` (`A_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=100001 ;

ALTER TABLE `B`
  ADD CONSTRAINT `B_ibfk_1` FOREIGN KEY (`A_id`) REFERENCES `A` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `C`
  ADD CONSTRAINT `C_ibfk_1` FOREIGN KEY (`A_id`) REFERENCES `A` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Ik denk niet dat je een primary key wil hebben op (id,A_id), maar gewoon op (id) aangezien je nooit op (id,A_id) zal zoeken, want dan heb je de hele rij al.

Edit: en je autoincrement snap ik ook niet, waarom niet gewoon 1?

[Verwijderd]

Hm, ik zet eigenlijk altijd een primary key op de auto_increment kolom.

De AUTO_INCREMENT staat al op een waarde omdat de code die je hierboven ziet gewoon de export is van phpmyadmin. De tabellen had ik voor de test gevuld met een hoop willekeurige data.

[mocean]

Ik denk dat een gewone PRIMARY KEY (`id`) beter zal werken dan PRIMARY KEY (`id`,`A_id`) , plus nog een INDEX op A_id. Misschien wordt de JOIN dan toch sneller...

[Verwijderd]

Zal het morgen eens testen (kan dan weer bij m'n testbak). Ben benieuwd!

Edit: vandaag nog getest, de resultaten waren niet veel anders. Het was overall ietsje sneller (~0.0008), maar de twee queries gingen qua gemiddelde executietijd weer gelijk op.

Is dit de query optimizer die hard z'n best heeft gedaan?

[ Voor 62% gewijzigd door Verwijderd op 13-01-2010 00:07 ]