[mysql] optimalisatie van query?

[Verwijderd]

Hallo allemaal,

Heb mezelf weer in een te ingewikkelde query gewerkt ;-) De query komt uit een apotheek voorraad systeem en heeft vrij veel artikelen. Bepaalde artikelen zijn weer gekoppeld aan een voorschrift.

Misschien dat hij eenvoudiger kan en zijn alle aanvullingen meegenomen. De DISTINCT zit er op omdat er dubbele resultaten uit de tabel 'voorschriften' komen.

PHP:

SELECT DISTINCT voorschrift.cfApotheek, voorschrift.component, voorschrift.recipesoort, voorschrift.soortInstelling, voorschrift.indicatieVervallen, voorschrift.T950tariefcode, apotheekArtikel.inkoopPrijs, apotheekArtikel.apotheekId, apotheekArtikel.artikelnummer, apotheekArtikel.ziNummer, artikel.etiketNaam, artikel.productGroep, artikel.hoeveelheidPerVerpakking, apotheekArtikel.voorraadWaarde, apotheekArtikel.laatsteInventarisatiedatum, apotheekArtikel.laatsteAfleverdatum, apotheekArtikel.laatsteBesteldatum, apotheekArtikel.laatsteOntvangstdatum, apotheekArtikel.datumLaatsteVoorschrift,  DATEDIFF (NOW(), laatsteInventarisatiedatum) AS aantalDagenInventarisatiedatum,  DATEDIFF (NOW(), laatsteAfleverdatum) AS aantalDagenAfleverdatum,  DATEDIFF (NOW(), laatsteBesteldatum) AS aantalDagenBesteldatum,  DATEDIFF (NOW(), laatsteOntvangstdatum) AS aantalDagenOntvangstdatum,  DATEDIFF (NOW(), datumLaatsteVoorschrift) AS aantalDagenLaatsteVoorschrift,  ( voorraadHoeveelheid / `hoeveelheidPerVerpakking` ) as aantalVerpakkingen  FROM apotheekArtikel LEFT JOIN artikel ON artikel.artikelnummer = apotheekArtikel.artikelnummer LEFT JOIN voorschrift ON (voorschrift.artikelnummer = apotheekArtikel.artikelnummer AND voorschrift.apotheekId = apotheekArtikel.apotheekId AND voorschrift.afleverdatum = apotheekArtikel.laatsteAfleverdatum) WHERE  ((apotheekArtikel.laatsteAfleverdatum = '0000-00-00' AND apotheekArtikel.laatsteOntvangstdatum <= '2016-09-14' ) OR  (apotheekArtikel.laatsteAfleverdatum != '0000-00-00' AND apotheekArtikel.laatsteAfleverdatum <= '2016-09-14' ))  AND apotheekArtikel.dateModified >= '2016-12-26' AND apotheekArtikel.artikelStatus = 'actief'  AND (apotheekArtikel.datumLaatsteVoorschrift = '0000-00-00' OR apotheekArtikel.datumlaatsteVoorschrift <= '2016-09-14') AND (voorschrift.indicatieVervallen IS NULL OR voorschrift.indicatieVervallen != 'D') AND (voorschrift.soortInstelling IS NULL OR voorschrift.soortInstelling != 'B') AND (voorschrift.cfApotheek IS NULL OR voorschrift.cfApotheek != 'cf') AND (voorschrift.recipeSoort IS NULL OR voorschrift.recipeSoort != '2') ORDER BY apotheekArtikel.voorraadWaarde DESC;

[MAX3400]

Ik snap je vraag niet; als je query werkt, dan werkt het. Daar valt weinig aan te optimaliseren. Daarnaast is het een enorme hoeveelheid parameters/colums die je opvraagt dus geen idee wat je huidige probleem nu is.

Als je voldoende indices hebt, kan je eventueel erover nadenken om je hele query op te breken in 3 of 4 queries en deze na elkaar "cross-referencen" voor je output.

[The Eagle]

Zet die query eens op een aantal regels ipv eentje. Dit is niet leesbaar en maakt het jezelf er oon niet makkelijker op
En wat wil je optimaliseren? Duurt het lang voor je een resultaatset krijgt? Al eens een explain plan gedraaid?

[Barreljan]

The Eagle schreef op donderdag 29 december 2016 @ 11:56:
Zet die query eens op een aantal regels ipv eentje. Dit is niet leesbaar en maakt het jezelf er oon niet makkelijker op
En wat wil je optimaliseren? Duurt het lang voor je een resultaatset krijgt? Al eens een explain plan gedraaid?

Exact mijn idee, waarom optimaliseren? Je ziet door de bomen het bos niet meer denk ik, dat is eerder een issue.


Hier met een paar simpele enters en spaties al meer zicht op de queury, maar voor alsnog geen direct probleem. Het is gewoon een flinke query maar geen lompe (eerst veel ophalen en met 5 dingen verder bijv.)

code:

SELECT DISTINCT 
  voorschrift.cfApotheek, voorschrift.component, voorschrift.recipesoort, voorschrift.soortInstelling,
  voorschrift.indicatieVervallen, voorschrift.T950tariefcode, apotheekArtikel.inkoopPrijs, apotheekArtikel.apotheekId,
  apotheekArtikel.artikelnummer, apotheekArtikel.ziNummer, artikel.etiketNaam, artikel.productGroep,
  artikel.hoeveelheidPerVerpakking, apotheekArtikel.voorraadWaarde, apotheekArtikel.laatsteInventarisatiedatum,
  apotheekArtikel.laatsteAfleverdatum, apotheekArtikel.laatsteBesteldatum, apotheekArtikel.laatsteOntvangstdatum,
  apotheekArtikel.datumLaatsteVoorschrift, 
  DATEDIFF (NOW(), laatsteInventarisatiedatum) AS aantalDagenInventarisatiedatum,
  DATEDIFF (NOW(), laatsteAfleverdatum) AS aantalDagenAfleverdatum, 
  DATEDIFF (NOW(), laatsteBesteldatum) AS aantalDagenBesteldatum, 
  DATEDIFF (NOW(), laatsteOntvangstdatum) AS aantalDagenOntvangstdatum, 
  DATEDIFF (NOW(), datumLaatsteVoorschrift) AS aantalDagenLaatsteVoorschrift, 
  ( voorraadHoeveelheid / `hoeveelheidPerVerpakking` ) AS aantalVerpakkingen 

FROM apotheekArtikel 

LEFT JOIN artikel ON artikel.artikelnummer = apotheekArtikel.artikelnummer 
LEFT JOIN voorschrift ON (voorschrift.artikelnummer = apotheekArtikel.artikelnummer 
 AND voorschrift.apotheekId = apotheekArtikel.apotheekId AND voorschrift.afleverdatum = apotheekArtikel.laatsteAfleverdatum) 

WHERE 
((apotheekArtikel.laatsteAfleverdatum = '0000-00-00' AND apotheekArtikel.laatsteOntvangstdatum <= '2016-09-14' ) OR
 (apotheekArtikel.laatsteAfleverdatum != '0000-00-00' AND apotheekArtikel.laatsteAfleverdatum <= '2016-09-14' )) 
 AND apotheekArtikel.dateModified >= '2016-12-26' 
 AND apotheekArtikel.artikelStatus = 'actief' 
 AND (apotheekArtikel.datumLaatsteVoorschrift = '0000-00-00' OR apotheekArtikel.datumlaatsteVoorschrift <= '2016-09-14')
 AND (voorschrift.indicatieVervallen IS NULL OR voorschrift.indicatieVervallen != 'D') 
 AND (voorschrift.soortInstelling IS NULL OR voorschrift.soortInstelling != 'B')
 AND (voorschrift.cfApotheek IS NULL OR voorschrift.cfApotheek != 'cf')
 AND (voorschrift.recipeSoort IS NULL OR voorschrift.recipeSoort != '2')

ORDER BY apotheekArtikel.voorraadWaarde DESC;

[Gonadan]

Sowieso zie ik veel negatieve filtering, da's nooit fijn voor indices. Kan je niet wat meer logica verhuizen naar code?

[Verwijderd]

Als je de apotheekArtikel WHERE clausules bij je 1e left join zet, filter je eerst al je voorschrift join.
Dat scheelt misschien nog wel een hoop?
[edit]
artikelstatus (ik zie er nog meer) is een tekst-kolom. Knal indexes op tekst-kolommen, of beter nog een numeriek veld met een foreign key naar de verwijzende 'statustabel'
Beetje normaliseren. Je kan dan ook makkelijker eigenschappen aan statussen koppelen zoals 'delivered', 'done' etc... waardoor je filtering dus makkelijker wordt (maar wel een extra join vereist...)

Laatste opmerking: waarom LEFT JOIN en geen INNER? Wat ik hierboven zeg over filtering, werkt natuurlijk wel alleen performance-verbeterend als de tweede join 'niet meer werkt' als niet voldaan is aan de eerste.

Volgens mij is het ook nog zo dat je het best eerste de grootste filtering kan laten geschieden; de overgebleven rows zijn dan lager waardoor opvolgende filtering sneller gaat... dus zelfs de volgorde van het WHERE statement maakt uit geloof ik.

[ Voor 103% gewijzigd door Verwijderd op 29-12-2016 12:42 ]

[Verwijderd]

Gonadan schreef op donderdag 29 december 2016 @ 12:30:
Sowieso zie ik veel negatieve filtering, da's nooit fijn voor indices. Kan je niet wat meer logica verhuizen naar code?

En 0000-00-00 vervangen voor NULL, dan hoef je niet meer te filteren bij SELECTs.
Als dat geen optie is moeten er kolommen bij. (delivery_status ofzo)

[ Voor 3% gewijzigd door Verwijderd op 29-12-2016 12:33 ]

[Hydra]

MAX3400 schreef op donderdag 29 december 2016 @ 11:44:
Ik snap je vraag niet; als je query werkt, dan werkt het. Daar valt weinig aan te optimaliseren.

Sorry maar wat? Dat een query werkt betekent niet dat het niet beter kan. Sowieso is een select distinct al een enorme code smell; je query levert kennelijk dubbelen op die je niet wil en dus laat je je DB engine dat maar oplossen. Daarnaast is dit een vrij enorme query met een hoop columns in de where clause dus is het een goed idee om te kijken of de query engine er wel van chocolade van weet te maken (dmv een explain) en te kijken of het herorderen van wat indices niet het een en andere op wil leveren.

Over het algemeen wil je zo snel mogelijk je result kleiner maken omdat het cartesisch product hard groeit als je een zwik tabellen joined. Dit 'kleiner' maken wil je zoveel mogelijk doen door indices te gebruiken. MySQL probeert dit te optimaliseren maar dat gaat niet altijd goed; daarom is het altijd slim om bij niet triviale queries de explain te bekijken.

[MAX3400]

Hydra schreef op donderdag 29 december 2016 @ 13:06:
[...]


Sorry maar wat?

Ik probeerde de topicstarter te hinten om te omschrijven/beschrijven waarom er gevraagd wordt om optimalisatie. Uit de initiele vraagstelling is absoluut niet duidelijk (voor leken en gevorderden) of de query te lang duurt of dat er een andere optimalisatie nodig kan zijn (zoals leesbaarheid).

Het enige wat de initiele vraagstelling laat zien is dat er optimalisatie zou kunnen plaatsvinden in het denk/programmeer-proces van topicstarter en helaas weet/zie ik niet of dit komt door een tekort aan kennis of mogelijk een sub-optimale inrichting van de databases/tabellen of zelfs oudere/legacy software.

[Hydra]

MAX3400 schreef op donderdag 29 december 2016 @ 13:10:
Ik probeerde de topicstarter te hinten om te omschrijven/beschrijven waarom er gevraagd wordt om optimalisatie.

Ik zou dat dan de volgende keer gewoon zo vragen i.p.v. een hint die heel anders opgevat kan worden