[MySQL]Gebruik van 'IN' clausule vermijden

[van.der.schulting]

Ik heb twee tabellen in MySQL:

code:

mails
+------------------+----------+------+-----+---------+----------------+
| Field            | Type     | Null | Key | Default | Extra          |
+------------------+----------+------+-----+---------+----------------+
| id               | int(11)  | NO   | PRI | NULL    | auto_increment |
send_time   | datetime | YES  |     | NULL    |                |
| advertisement_id | int(11)  | YES  | MUL | 0       |                |
+------------------+----------+------+-----+---------+----------------+

advertisements
+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
+---------------------+--------------+------+-----+---------+----------------+

Korte toelichting:
1. N.a.v. een advertentie wordt een mail verstuurd. De verzenddatum wordt opgeslagen in de tabel 'mails'
2.. Velden die niet relevant zijn heb ik voor het gemak weggelaten. De tabellen zijn in werkelijkheid veel uitgebreider.

Anders gezegd:
Een advertisement has many mails
EN
Een mail belongs to an advertisement

Ik wil alle advertenties tevoorschijn halen waarvan in de afgelopen X tijdsperiode geen mails verstuurd zijn.
Ik kom tot de volgende query:

code:

SELECT `advertisements`.* FROM `advertisements`  
WHERE (id NOT IN 
  (
  SELECT 
  DISTINCT(advertisement_id) 
  FROM mails 
  WHERE send_time > '#{n.days.ago}'
  )
)

Toelichting:
1. '#{n.days.ago}' is Ruby-code die een tijdsstempel genereert die MySQL snapt

Bovenstaande query doet wat ik wil, maar is natuurlijk best 'zwaar'. Ik heb er lang over nagedacht, maar kan niet tot een oplossing komen die efficienter is.
Mijn probleem is dat mijn kennis van SQL enigzins tekort schiet. Hierdoor ben ik bang dat ik uitindelijk een query bouw die bij tests lijkt te werken (door toeval), maar in werkelijkheid niet precies doet wat ik wil.
Van bovenstaande query ben ik er van overtuigd dat die werkt, omdat ik de query volledig 'doorzie'.

Ik zat zelf te denken in de volgende richting, maar ik kom niet tot de goede oplossing:
- Een INNER JOIN die alleen de eerste rij ophaalt van mails gesorteerd op send_time aflopend en dan vervolgens een WHERE clausule 'send_time < #{n.days.ago}'. Kan ik echter wel een CLAUSULE op een INNER JOIN zetten?
Denk ik in de goede richting? Wie kan me helpen tot een efficientere oplossing te komen, zonder dat ik een query bouw die 'bij toeval werkt', maar in werkelijkheid niet doe wat ik wil.

[Sikkek]

Ik zou een LEFT JOIN op mails doen en in de WHERE zetten dat mails.advertisementsId NULL moet zijn (en nog een distinct in je SELECT clause)

[NMe]

Dat inderdaad, maar wat zou er mis moeten zijn met een where clause in een query met een join erin? En waarom moet het per se een oplossing zonder IN zijn? EXISTS zal beter performen dan IN maar ik kan nou niet bepaald uit je topicstart halen of dat de reden is.

[GlowMouse]

SHOW CREATE TABLE is 10x duidelijker dan die rare tabelletjes. Ik kan nu niet zien wat de indexen zijn.

LEFT JOIN en EXISTS zijn even snel.

Het probleem is dat je verwacht dat de subquery maar 1x wordt uitgevoerd, maar in werkelijkheid wordt die voor elke advertisement uitgevoerd.

[ACM]

GlowMouse schreef op dinsdag 28 februari 2012 @ 13:04:
Het probleem is dat je verwacht dat de subquery maar 1x wordt uitgevoerd, maar in werkelijkheid wordt die voor elke advertisement uitgevoerd.

Dat is niet per se waar en voor deze specifieke subquery ook niet nodig. Of het daadwerkelijk wel of niet gebeurd is bij MySQL uiteraard altijd nogal tricky.

Maar ik zou in dit geval in ieder geval dit proberen:

code:

NOT EXISTS (SELECT * FROM mails WHERE advertisement_id = advertisements.id AND send_time > X)

Een alternatief kan zijn om een temporary table van die hele in te maken en daarna met eenzelfde not exists (of left join+is null) te kijken hoe het er voor staat. Soms is MySQL dan ineens wel in staat efficient te werken. Niet vergeten dat je ook op temp tables indexen en PK's mag leggen, gelijk in de create al, a la:

code:

CREATE TEMPORARY TABLE last_weeks_mails (PRIMARY KEY(id)) AS
SELECT DISTINCT advertisement_id as id FROM mails WHERE send_time > X

En dan dus met die last_weeks_mails joinen (kan dus via left join met een null-check, not exists of je huidige not in).

[GlowMouse]

ACM schreef op dinsdag 28 februari 2012 @ 22:12:
[...]

Dat is niet per se waar en voor deze specifieke subquery ook niet nodig. Of het daadwerkelijk wel of niet gebeurd is bij MySQL uiteraard altijd nogal tricky.

Uit mijn geheugen zei ik van niet, maar deze post spreekt deze post tegen (tenzij IN en NOT IN anders zijn geïmplementeerd, wat ik niet denk).

Maar indices zijn heel belangrijk, en juist die zien we hier niet terug.

[ Voor 5% gewijzigd door GlowMouse op 28-02-2012 23:18 ]