[MySQL] Efficient uit twee tabellen selecteren

[RwD]

Ik probeer een efficientere code te vinden voor een selectie uit twee tabellen.

• Tabel 1 (products) bevat productinfo, tabel 2 (stock) bevat leverancier, voorraad en prijs.
• Voor ieder product zijn er 1 of meerdere leveranciers met verschillende voorraad en verschillende prijzen.
• Het veld genaamd ean koppelt de records uit beide tabellen (foreign key)
• De klant krijgt de laagste prijs, maar totale voorraad te zien.
• Er kan door klanten een selectie gemaakt worden, bijv kleur=rood. Deze gegevens zitten alleen volledig juist in de products table.
• Products bevat 8000 rijen, stock bevat 18000 rijen

Ik heb meerdere queries die werken, maar geen die snel genoeg is EN ook nog helemaal juist is.

code:

SELECT p.*, totalStock, minprice
FROM products p
LEFT OUTER JOIN (SELECT s.ean, SUM(sstock) as totalStock, min(price) as minprice FROM stock s GROUP BY s.ean) sp
on sp.ean = p.ean
ORDER BY sp.minprice

Deze code werkt, en haalt exact op wat ik wil.
Nadeel: 12 seconden om uit te voeren.

code:

SELECT p . * , c.totalStock, c.minprice
FROM products p
INNER JOIN (SELECT ean, SUM(sstock ) AS totalStock, MIN( price ) AS minprice
FROM stock
GROUP BY ean
) c ON p.ean = c.ean
ORDER BY c.minprice

Deze code werkt, haalt op wat ik wil.
Nadeel: 0,4 seconden om uit te voeren

code:

SELECT p.*, c.totalStock, c.minprice
FROM (SELECT SUM(sstock) AS totalStock, ean, MIN(price) AS minprice FROM stock GROUP BY ean) c
INNER JOIN products p ON p.ean = c.ean
ORDER BY c.minprice

Werkt.
Klein nadeel: 0,1 seconden

Nu moet ik deze query 2 keer uitvoeren, en ik vraag me af of de query wel zo efficient is omdat ik in de subquery eerst alle 18000 records door moet lopen...

[Hipska]

Volgens mij kan je die query ook uitvoeren zonder die subquery erbij als je correcte JOINs doet.
Iets als dit?

code:

SELECT p.*, SUM(s.stock), MIN(s.price) as minprice
FROM products p
JOIN stock s
USING(san)
GROUP BY p.ean
ORDER BY minprice

[SPee]

Heb je wel indexen en foreign keys op die kolommen?

De tabellen zijn niet zo groot en de secondenwerk het volledig doorlopen van de records kan zijn. Als die geheel in het geheugen zijn geladen zou het nog snel zijn. Als je database echt groot wordt zodat dat niet meer kan, dan zul je opeens een dramatische performanceverlies krijgen.

[RwD]

Hipska schreef op vrijdag 05 oktober 2012 @ 10:28:
Volgens mij kan je die query ook uitvoeren zonder die subquery erbij als je correcte JOINs doet.
Iets als dit?

code:

SELECT p.*, SUM(s.stock), MIN(s.price) as minprice
FROM products p
JOIN stock s
USING(san)
GROUP BY p.ean
ORDER BY minprice

Verbazingwekkend simpele query, dat ik die nog niet geprobeerd had... Maar die draait in gemiddeld 0,6 seconden (10 keer getest), dus die is wel langzamer. Ik vermoed dat hij voor ieder record de tweede tabel opnieuw doorzoekt?

edit:
Dat die group by een ongewenst resultaat zou hebben als ik andere velden er bij wil hebben was me nog niet opgevallen. In dit geval zou het wel goed werken

[ Voor 12% gewijzigd door RwD op 05-10-2012 10:40 ]

[RobIII]

Hipska schreef op vrijdag 05 oktober 2012 @ 10:28:
Volgens mij kan je die query ook uitvoeren zonder die subquery erbij als je correcte JOINs doet.
Iets als dit?

code:

SELECT p.*, SUM(s.stock), MIN(s.price) as minprice
FROM products p
JOIN stock s
USING(san)
GROUP BY p.ean
ORDER BY minprice

Hoe werkt dat GROUP BY nu eigenlijk?
Leuk dat je query in MySQL werkt maar er klopt geen drol van

[RwD]

SPee schreef op vrijdag 05 oktober 2012 @ 10:36:
Heb je wel indexen en foreign keys op die kolommen?

De tabellen zijn niet zo groot en de secondenwerk het volledig doorlopen van de records kan zijn. Als die geheel in het geheugen zijn geladen zou het nog snel zijn. Als je database echt groot wordt zodat dat niet meer kan, dan zul je opeens een dramatische performanceverlies krijgen.

Wel indexen op ean, price en stock. Maar een foreign key kan volgens mij niet (toch?) omdat de db engine MyISAM is en ik die niet kan wijzigen in InnoDb.

[Wolfboy]

Geef voor het gemak eens een explain van Hipska's query (en probeer hem correct te maken zoals RobIII al aangeeft).

[Hipska]

RobIII heeft gelijk, de query moet nog juist gemaakt worden om GROUP BY correct te laten werken, dit was louter een snel voorbeeld die in MySQL blijkbaar wel werkt.

Geen foreign keys? Dan is dat mogelijks de bottleneck.
En waarom zou je niet naar InnoDB kunnen gaan?
Maak een nieuwe tabel aan en zet de inhoud over..

[RwD]

Hipska, ik bepaal niet welke db engine gebruikt wordt omdat dit een onderdeel is van een groter systeem waarin ik niks mag wijzigen.

Als ik jouw code herschrijf kom ik uit op mijn tweede query, tenzij dat ik het helemaal niet begrepen heb levert het herschrijven me maar 0,2 winst op en blijft niet de snelste. Ik heb overigens wel een index toegevoegd nav de EXPLAIN. Dit versnelde jouw query voorbeeld met 0,05 gemiddeld. Mijn snelste query wordt er 0,003 gemiddeld sneller van (als dat echt een meetbaar resultaat is).

[Wolfboy]

Hipska schreef op vrijdag 05 oktober 2012 @ 11:37:
Geen foreign keys? Dan is dat mogelijks de bottleneck.
En waarom zou je niet naar InnoDB kunnen gaan?
Maak een nieuwe tabel aan en zet de inhoud over..

MyISAM ondersteund geen foreign key constraints, behalve de constraints zijn foreign keys natuurlijk weinig meer dan gewone indexes

Dat zou dus geen probleem moeten zijn lijkt me.

[Hipska]

Ik begrijp je niet helemaal goed denk ik. Ik lees dit:

RwD schreef op vrijdag 05 oktober 2012 @ 12:02:
Hipska, ik bepaal niet welke db engine gebruikt wordt omdat dit een onderdeel is van een groter systeem waarin ik niks mag wijzigen.

En even verder zie ik dit:

Ik heb overigens wel een index toegevoegd nav de EXPLAIN.

Dan mag je blijkbaar wel dingen wijzigen? Of toch eigenlijk niet?

[RwD]

@Wolfboy, ok, dan had ik een index en is dit wellicht het snelst??

Hipska schreef op vrijdag 05 oktober 2012 @ 14:42:
Ik begrijp je niet helemaal goed denk ik. Ik lees dit:

[...]

En even verder zie ik dit:

[...]

Dan mag je blijkbaar wel dingen wijzigen? Of toch eigenlijk niet?

Ja, ik mag velden en indexes toevoegen. Ik maak gebruik van een systeem waar bijna alles kunt wijzigen. Maar wat db betreft kun je in deze versie alleen toevoegen. In toekomstige versie kan ik ook db wijzigen met extensies.

[Wolfboy]

RwD schreef op zaterdag 06 oktober 2012 @ 14:07:
@Wolfboy, ok, dan had ik een index en is dit wellicht het snelst??

Mogelijk.

Nogmaals, heb je de explain output?

Daarnaast, mogelijk kan je MySQL server ook wel wat tuning gebruiken om dit soort dingen sneller te kunnen doen

[ACM]

Wolfboy schreef op vrijdag 05 oktober 2012 @ 13:35:
MyISAM ondersteund geen foreign key constraints, behalve de constraints zijn foreign keys natuurlijk weinig meer dan gewone indexes

Foreign keys zijn geen performance-middel. Sterker nog, de foreign key van X naar Y ligt bij tabel X terwijl je op Y snel wilt selecteren.

Foreign key constraints hebben bovendien eigenlijk niet zoveel te maken met indexen... Het wordt wel vaak via de primary key van de 'andere kant' gedaan, maar er is formeel geen verplichting dat te doen. Uiteraard denkt MySQL daar anders over, met InnoDB ben je vziw verplicht als je een Foreign Key wilt gebruiken om dan ook bij de tabel Y een index op die kolom te hebben.

Desalniettemin is het niet echt relevant voor deze discussie. Je wilt een index op ean hebben in de stock-tabel en waarschijnlijk een PK erop in products, de FK is puur/met name voor data-integriteit relevant.

[Wolfboy]

ACM schreef op zaterdag 06 oktober 2012 @ 18:27:
[...]

Foreign keys zijn geen performance-middel. Sterker nog, de foreign key van X naar Y ligt bij tabel X terwijl je op Y snel wilt selecteren.

Absoluut, maar laten we het niet complexer maken dan nodig. Bij grote databases met veel transacties kan het zelfs zinnig zijn om foreign keys te droppen aangezien ze inserts/updates/deletes op de linked tabellen kunnen blokkeren. Maar dat gaat iets te ver in voor deze discussie imho

Foreign key constraints hebben bovendien eigenlijk niet zoveel te maken met indexen... Het wordt wel vaak via de primary key van de 'andere kant' gedaan, maar er is formeel geen verplichting dat te doen. Uiteraard denkt MySQL daar anders over, met InnoDB ben je vziw verplicht als je een Foreign Key wilt gebruiken om dan ook bij de tabel Y een index op die kolom te hebben.

Uiteraard, een foreign key is natuurlijk niet meer dan een constraint. Maar nagenoeg iedereen die het over een foreign key heeft zal bedoelen dat er een constraint + index is. De index aan de andere kant is een andere vraag maar (imho) common sense aangezien je het normaliter over een primary key zal hebben daar.

[RwD]

Ik moest het helaas complexer maken met levertijden gebaaseerd op de huidige tijd. De query die ik van ACM heb gekregen is sneller dan bovenstaande voorbeelden en lost meteen het probleem op waar aanvankelijk niet uit kwam. Het enige probleem waar ik wel uit ga komen is dat ik nog een sum(stock) moet hebben.

code:

SELECT ...
FROM product p 
 JOIN productstock sp USING (ean)
WHERE sp.id = 
  IFNULL((SELECT psi.id 
  FROM productstock psi 
    JOIN supplier s ON psi.supId = s.id
  WHERE psi.ean = p.ean
  AND stock>0
  ORDER BY IF(sup.orderBefore>40667, sup.deliveryTime, sup.deliveryTime+86400), price
  LIMIT 1), (SELECT psi.id 
  FROM productstock psi 
    JOIN supplier s ON psi.supId = s.id
  WHERE psi.ean = p.ean
  ORDER BY IF(sup.orderBefore>40667, sup.deliveryTime, sup.deliveryTime+86400), price
  LIMIT 1))
ORDER BY sp.price

0,0264 sec (0,0050 als zonder te sorteren)

[ Voor 5% gewijzigd door RwD op 07-10-2012 23:43 ]