[SQL]JOIN onder bepaalde condities

[van.der.schulting]

Ik heb twee tabellen (overbodige kolommen heb ik weggelaten)

code:

advertisements | CREATE TABLE `advertisements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `send_mail` tinyint(1) DEFAULT '0',
  `removed` tinyint(1) DEFAULT '0',
}

| mails | CREATE TABLE `dmca_mails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mail_send_time` datetime DEFAULT NULL,
  `advertisement_id` int(11) DEFAULT '0',
}

Ik wil een query bouwen die het volgende resultaat geeft:
Alle advertenties die niet verwijderd zijn EN waar send_mail op 1 staat
+ Alle advertenties die niet verwijderd zijn EN waar de laatste mail een week geleden verzonden is.

Dit gedeelte is simpel:
Alle advertenties die niet verwijderd zijn EN waar send_mail op 1 staat.

code:

SELECT `advertisements`.* FROM `advertisements` 
WHERE (send_mail = 1 AND removed = 0)

Het tweede gedeelte toevoegen kom ik niet uit:
Alle advertenties die niet verwijderd zijn EN waar send_mail op 1 staat
+ Alle advertenties die niet verwijderd zijn EN waar de laatste mail een week geleden verzonden is.
Ik dacht hieraan

code:

SELECT `advertisements`.* FROM `advertisements` 
LEFT OUTER JOIN mails ON advertisements.id = mails.advertisement_id 
AND mail_send_time < <7 DAGEN GELEDEN> 
WHERE (send_mail = 1 AND removed = 0)

Deze query geeft natuurlijk doodleuk de advertenties weer waarvoor in de afgelopen week WEL een mail is verstuurd en die wil ik er juist uitfilteren. M.a.w. in dit geval slaat de LEFT OUTER JOIN nergens op... Ik kom er alleen niet uit hoe het wel moet. Wie kan me helpen?

[TheNephilim]

Is het niet makkelijker om `mail_send_time` gewoon bij `advertisements` in te zetten?

[Skinny]

Dus samengevat :

- Alle ads met hun eventueel laatste mail_send_time
- Waar removed = 0
- En (send_mail = 1 OF laatste mail_send_time < 7 dagen geleden)

levert :

SQL:

SELECT a.id, a.send_mail, MAX(mail_send_time) AS last_mail_send_time
FROM advertisements a
    LEFT OUTER JOIN dmca_mails m ON (a.id = m.advertisement_id)
WHERE a.removed = 0
GROUP BY a.id, a.send_mail, a.removed
HAVING 
(a.send_mail = 1 OR MAX(mail_send_time) < '2012/3/12')
 

(disclaimer: MSSQL syntax, dus wellicht even omzetten her en der)

[GlowMouse]

Skinny schreef op maandag 19 maart 2012 @ 18:18:
Dus samengevat :

- Alle ads met hun eventueel laatste mail_send_time
- Waar removed = 0
- En (send_mail = 1 OF laatste mail_send_time < 7 dagen geleden)

levert :

SQL:

SELECT a.id, a.send_mail, MAX(mail_send_time) AS last_mail_send_time
FROM advertisements a
    LEFT OUTER JOIN dmca_mails m ON (a.id = m.advertisement_id)
WHERE a.removed = 0
GROUP BY a.id, a.send_mail, a.removed
HAVING 
(a.send_mail = 1 OR MAX(mail_send_time) < '2012/3/12')
 

(disclaimer: MSSQL syntax, dus wellicht even omzetten her en der)

Een oplossing met EXISTS zal sneller werken.

[pedorus]

van.der.schulting schreef op maandag 19 maart 2012 @ 17:07:
Alle advertenties die niet verwijderd zijn EN waar send_mail op 1 staat
+ Alle advertenties die niet verwijderd zijn EN waar de laatste mail een week geleden verzonden is.
Ik dacht hieraan

code:

SELECT `advertisements`.* FROM `advertisements` 
LEFT OUTER JOIN mails ON advertisements.id = mails.advertisement_id 
AND mail_send_time < <7 DAGEN GELEDEN> 
WHERE (send_mail = 1 AND removed = 0)

Deze query geeft natuurlijk doodleuk de advertenties weer waarvoor in de afgelopen week WEL een mail is verstuurd en die wil ik er juist uitfilteren.

Deze left-outer join daar doe je inderdaad niets mee, en daarnaast staat het > teken verkeertenom. Als je de conditie die nu in de AND staat naar de WHERE verplaatst, en eventueel DISTINCT toevoegd, dan gaat het wel goed. Je kunt het effect van de conditie eenvoudig eens bekijken door de laatste where weg te halen.

Condities van het type (A&&B) || (A&&C) kun je herschrijven naar A && (B || C). Dat geeft hier dus removed = 0 and (send_mail = 1 or (mail_send_time > now() - interval 1 week).

[Skinny]

GlowMouse schreef op maandag 19 maart 2012 @ 20:48:
[...]

Een oplossing met EXISTS zal sneller werken.

[offtopic] serieus ? Ik dacht eigenlijk altijd dat het andersom was?

[pedorus]

Exists is een join-type, terwijl die MAX(...) waarschijnlijk niet goed begrepen wordt door mysql. Maar zoals altijd: meten == weten (hoewel explain ook kan helpen).

[Skinny]

pedorus schreef op maandag 19 maart 2012 @ 22:40:
Exists is een join-type, terwijl die MAX(...) waarschijnlijk niet goed begrepen wordt door mysql. Maar zoals altijd: meten == weten (hoewel explain ook kan helpen).

Vandaar.. ik werk nauwelijks met MySQL, veel met MSSQL, ga toch eens kijken of het daar ook geldt..

[GlowMouse]

Skinny schreef op maandag 19 maart 2012 @ 22:36:
[...]


[offtopic] serieus ? Ik dacht eigenlijk altijd dat het andersom was?

LEFT JOIN icm IS NULL is wel snel, maar dat lukt niet bij deze query zonder ook de GROUP BY. In de query die je gaf zit een GROUP BY. MySQL gaat dan eerst sorteren op de kolommen in je GROUP BY en hoewel dat hier met een index op (id,send_mail,removed) kan, verwacht ik toch dat NOT EXISTS sneller is. Dat komt omdat NOT EXISTS kan stoppen zodra hij één record heeft gevonden (wat met een index op (advertisement_id,mail_send_time) heel snel kan), terwijl jouw join veel meer rijen op moet halen om vervolgens het maximum te vinden. Een NOT EXISTS heeft geen index op de GROUP BY-kolommen nodig om toch snel te draaien.

pedorus schreef op maandag 19 maart 2012 @ 20:57:
[...]
Condities van het type (A&&B) || (A&&C) kun je herschrijven naar A && (B || C).

Ik kan het niet terugvinden, maar ik dacht eens gelezen te hebben dat ze in MySQL voor indexgebruik niet identiek zijn omdat MySQL niet ziet dat ze identiek zijn.