[MySQL 4.1] Probleem met subselect

[Verwijderd]

Ik heb een website die gebuik maakt van een PostgreSQL database.

Door het toch wel grote snelheidsverschil tussen Postgres en MySQL wil ik alle data vanuit Postgres omzetten naar MySQL.

Het overzetten van de data is geen probleem, maar de queries wel. Het lijkt erop dat MySQL 4.1 toch nog niet helemaal goed overweg kan met subselects, of ik kijk ergens overheen.

Het gaat om onderstaande query :

PHP:

SELECT team_id, (
  SELECT count( thuis_uitslag )
  FROM wed_zaterdag1_s0203
  WHERE thuis_wed = t.team_id OR uit_wed = t.team_id
  ) AS wd
  FROM teams_s0203 t
WHERE klasse_id = 1

Deze query geeft in Postgres het gewenste resultaat, nml : 12 rijen met de waarde 22.
Het resultaat in MySQL is : 1 rij met de waarde 22 en 22 rijen met de waarde NULL

In de manual van MySQL staat nauwelijks iets over het gebruik van subselects in. Het enige wat ik daar heb gevonden is info over het herschrijven van een subselect naar een query met joins.


De tabel wed_zaterdag1_s0203 ziet er als volgt uit :

PHP:

id           mediumint(9)
wd            mediumint(9)
datum      date
thuis_wed     tinyint(4)
uit_wed      tinyint(4)
thuis_uitslag smallint(6)
uit_uitslag   smallint(6)
verslag      text
opmerking     text
titel       text

Wil ik te veel doen met MySQL of zit er een foutje in de query ??

[ACM]

Verwijderd schreef op 24 mei 2003 @ 18:03:
Door het toch wel grote snelheidsverschil tussen Postgres en MySQL wil ik alle data vanuit Postgres omzetten naar MySQL.

Hoe groot? Waarin?
Doe je wel eens vacuum analyze op postgres, heb je wel indices?

Het zou me verbazen als dat een verschil is waar je echt last van hebt in de praktijk, tenzij je de database totaal niet getuned hebt en de tabellen inefficient gebruikt en niet voorzien hebt van indices

Het overzetten van de data is geen probleem, maar de queries wel. Het lijkt erop dat MySQL 4.1 toch nog niet helemaal goed overweg kan met subselects, of ik kijk ergens overheen.

4.1 is dan ook niet voor niets nog beta

PHP:

SELECT team_id, (
  SELECT count( thuis_uitslag )
  FROM wed_zaterdag1_s0203
  WHERE thuis_wed = t.team_id OR uit_wed = t.team_id
  ) AS wd
  FROM teams_s0203 t
WHERE klasse_id = 1

't Kan aan mij liggen hoor, maar is een tabel met de naam wed_zaterdag1_s0203 niet een beetje vreemd en is waarschijnlijk een indicator dat je een heel slecht database ontwerp hebt? Hoeveel tabellen heb je wel niet?

Deze query geeft in Postgres het gewenste resultaat, nml : 12 rijen met de waarde 22.
Het resultaat in MySQL is : 1 rij met de waarde 22 en 22 rijen met de waarde NULL

Blijkbaar joined mysql de rijen niet aan elkaar of op een andere manier dan postgresql en echt vreemd is dat niet want jij geeft geen expliciete methode op om team_id aan t te koppelen... Dat heb je wel, sorry

[ Voor 10% gewijzigd door ACM op 24-05-2003 18:12 ]

[Verwijderd]

ACM schreef op 24 May 2003 @ 18:09:

Hoe groot? Waarin?
Doe je wel eens vacuum analyze op postgres, heb je wel indices?

Als ik de benchmarks mag geloven in MySQL zo'n 2 a 3 keer sneller dan Postgres met een select.

Ik heb geen indices.

4.1 is dan ook niet voor niets nog beta

Het is zelfs nog een Alpha

't Kan aan mij liggen hoor, maar is een tabel met de naam wed_zaterdag1_s0203 niet een beetje vreemd en is waarschijnlijk een indicator dat je een heel slecht database ontwerp hebt? Hoeveel tabellen heb je wel niet?

Ik heb zo'n 30 van deze tabellen

wed_zaterdag1_s0203
wed_zaterdag2_s0203

enz....

Het zijn tabellen met daarin een wedstrijdoverzicht van het elftal zaterdag1 in het seizoen 2002/2003, voor de overige tabellen geldt hetzelfde.

Ik heb er al eens over zitten denken om dit ontwerp te veranderen in :

wedstrijden_s0203 met daarin een extra kolom die het elftal nummer aangeeft.

[ACM]

Verwijderd schreef op 24 mei 2003 @ 18:29:
Als ik de benchmarks mag geloven in MySQL zo'n 2 a 3 keer sneller dan Postgres met een select.

Van mij mag je ze niet geloven
Benchmarks zijn altijd subjectief... Er is totaal geen reden over te stappen naar een andere database als de huidige goed voldoet, het levert altijd veel werk op namelijk.

Als postgresql nou te traag zou zijn en na vele slimme optimalisaties nog steeds te traag, dan zou je es naar mysql kunnen gaan kijken. Maar mysql mist dan ook een hele rits functionaliteit (zie linkje in mijn signature) die postgres wel biedt.

Het is best mogelijk (en in een paar gevallen ook wel zo) dat mysql sneller is dan postgresql, maar ik vermoed dat als jij performance problemen hebt met je database dat je die met je huidige ontwerp net zo hard ook krijgt met mysql...
Je hebt domweg te veel structurele problemen in je database ontwerp daarvoor, denk ik

Ik heb geen indices.

Als de boel nu traag is heb je gelijk de reden te pakken waarom
Je hebt vast ook nog nooit vacuum analyze uitgevoerd om de database op te schonen en de interne statistieken bij te werken

Het is zelfs nog een Alpha

En daarmee gelijk een hele goede reden om niet (of iig nog niet) naar over te stappen

Ik heb zo'n 30 van deze tabellen

wed_zaterdag1_s0203
wed_zaterdag2_s0203

enz....

Het zijn tabellen met daarin een wedstrijdoverzicht van het elftal zaterdag1 in het seizoen 2002/2003, voor de overige tabellen geldt hetzelfde.

Ik heb er al eens over zitten denken om dit ontwerp te veranderen in :

wedstrijden_s0203 met daarin een extra kolom die het elftal nummer aangeeft.

Dat is nog steeds niet zo netjes...

Als je voor iets meerdere exact dezelfde tabellen (kwa structuur) nodig hebt, maar waarvan het simpele feit dat een record in tabel A zit ipv B al betekend dat er automatisch meer over dat record bekend is dan is er wat fout in je ontwerp

Het gaat niet altijd op, maar in principe wel en zeker ook in dit geval.

Dergelijke tabelstructuren kunnen je applicaties onnodig lastig maken en zorgen er ook voor dat allerlei interessante queries niet of nauwelijks uit te voeren zijn (stel je wilt weten hoe goed team 1 het doet en daarvoor van elke periode een resultaat tonen... doe je dan 30 queries? of zou je het liever in 1 doen (en dan hebben we het niet over union ) ? )

[ Voor 8% gewijzigd door ACM op 24-05-2003 19:06 ]

[Verwijderd]

ACM schreef op 24 mei 2003 @ 19:04:

Je hebt vast ook nog nooit vacuum analyze uitgevoerd om de database op te schonen en de interne statistieken bij te werken

Nee. Ik ga eens kijken wat dat doet en of het iets uitmaakt in snelheid.

Dat is nog steeds niet zo netjes...

Als je voor iets meerdere exact dezelfde tabellen (kwa structuur) nodig hebt, maar waarvan het simpele feit dat een record in tabel A zit ipv B al betekend dat er automatisch meer over dat record bekend is dan is er wat fout in je ontwerp

Daarom is het toch goed om in een nieuw ontwerp alle data van alle teams in 1 tabel te gooien
Dan is er maar 1 tabel, ipv. de 30 die er nu zijn.

Dergelijke tabelstructuren kunnen je applicaties onnodig lastig maken en zorgen er ook voor dat allerlei interessante queries niet of nauwelijks uit te voeren zijn (stel je wilt weten hoe goed team 1 het doet en daarvoor van elke periode een resultaat tonen... doe je dan 30 queries? of zou je het liever in 1 doen (en dan hebben we het niet over union ) ? )

De resultaten van een periode berekenen is geen probleem, kijk maar op deze pagina :
http://www.quicksteps.nl/...seID=1&type=1&soort=tabel

Ik doe dat met 1 query.

[ACM]

Verwijderd schreef op 24 May 2003 @ 19:21:
Nee. Ik ga eens kijken wat dat doet en of het iets uitmaakt in snelheid.

Als er geen indices zijn die ie door de analyze kan gaan preferen zal er weinig verschil zijn. vacuum full analyze zal nog wel wat schelen, aangezien daardoor de database bestanden fysiek echt opgeruimd en verkleind worden.

Daarom is het toch goed om in een nieuw ontwerp alle data van alle teams in 1 tabel te gooien
Dan is er maar 1 tabel, ipv. de 30 die er nu zijn.

Mja, meerdere tabellen is geen probleem, dat is zelfs goed. Maar tabellen gebruiken om onderscheid in data dat je in een record zou moeten stoppen te maken is doorgaans niet goed.
Dus de data van je team (naam, etc) in een aparte tabel, van je wedstrijden, etc. Maar niet per seizoen apart de wedstrijden gaan bijhouden in losse tabellen

De resultaten van een periode berekenen is geen probleem, kijk maar op deze pagina :
http://www.quicksteps.nl/...seID=1&type=1&soort=tabel

Ik doe dat met 1 query.

Is die hele tabel met 1 query gegenereerd?
En wat als je overzichten van 10 jaar lang wilt? Voor een losse periode is het uiteraard vrij simpel

[Verwijderd]

ACM schreef op 24 May 2003 @ 19:27:

Als er geen indices zijn die ie door de analyze kan gaan preferen zal er weinig verschil zijn. vacuum full analyze zal nog wel wat schelen, aangezien daardoor de database bestanden fysiek echt opgeruimd en verkleind worden.

Wat voor data wordt er uit de tabellen vewijderd tijdens een schoonmaak ?
Zijn dit vooral spaties en lege regels ??

Mja, meerdere tabellen is geen probleem, dat is zelfs goed. Maar tabellen gebruiken om onderscheid in data dat je in een record zou moeten stoppen te maken is doorgaans niet goed.
Dus de data van je team (naam, etc) in een aparte tabel, van je wedstrijden, etc. Maar niet per seizoen apart de wedstrijden gaan bijhouden in losse tabellen

De namen van de teams staan in een apparte tabel. In die tabel heeft iedere team een id-nummer, dat id-nummer heeft een relatie met met de kolommen thuis_wed en uit_wed in de wedsrijd-tabel.

Is die hele tabel met 1 query gegenereerd?

Ja

PHP:

SELECT naam,team_id,( 
  SELECT count (thuis_uitslag) 
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks 
  WHERE wd <= '$per1_2' AND (thuis_wed = t.team_id OR uit_wed = t.team_id)) AS wd, ( 

  SELECT count(t.team_id) 
  FROM  wed_".$teamnaam_q."_s".$seizoenID."$reeks w 
  WHERE wd <= '$per1_2' AND (thuis_wed = t.team_id AND thuis_uitslag >      
    uit_uitslag OR uit_wed = t.team_id AND thuis_uitslag < uit_uitslag) ) AS gw, (  

  SELECT count(t.team_id) 
  FROM  wed_".$teamnaam_q."_s".$seizoenID."$reeks w 
  WHERE wd <= '$per1_2' AND (thuis_wed = t.team_id AND thuis_uitslag = 
    uit_uitslag OR uit_wed = t.team_id AND thuis_uitslag = uit_uitslag) ) AS gl, ( 

  SELECT count(t.team_id) 
  FROM      ed_".$teamnaam_q."_s".$seizoenID."$reeks w 
  WHERE wd <= '$per1_2' AND(thuis_wed = t.team_id AND thuis_uitslag < 
    uit_uitslag OR uit_wed = t.team_id AND thuis_uitslag > uit_uitslag) ) AS vl, (( 

  SELECT count(t.team_id) 
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks w 
  WHERE wd <= '$per1_2' AND (thuis_wed = t.team_id AND thuis_uitslag >      
    uit_uitslag OR uit_wed = t.team_id AND thuis_uitslag < uit_uitslag ) )*3)+((

  SELECT count(t.team_id) 
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks w 
  WHERE wd <= '$per1_2' AND (thuis_wed = t.team_id AND thuis_uitslag =      
    uit_uitslag OR uit_wed = t.team_id AND thuis_uitslag = uit_uitslag) )*1) AS pnt, (( 

  SELECT sum(thuis_uitslag) 
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks 
  WHERE wd <= '$per1_2' AND (thuis_wed = t.team_id ))*1)+(( 

  SELECT sum(uit_uitslag)       
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks 
  WHERE wd <= '$per1_2' AND (uit_wed = t.team_id ))*1) AS dlpv, (( 

  SELECT sum(thuis_uitslag) 
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks 
  WHERE wd <= '$per1_2' AND (uit_wed = t.team_id ))*1)+((       

  SELECT sum(uit_uitslag) 
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks 
  WHERE wd <= '$per1_2' AND (thuis_wed = t.team_id ))*1) AS dlpt, ( ((

  SELECT sum(thuis_uitslag) 
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks  
  WHERE wd <= '$per1_2' AND (thuis_wed = t.team_id) ) + (

  SELECT sum(uit_uitslag) 
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks 
  WHERE wd <= '$per1_2' AND (uit_wed = t.team_id))) - ((

  SELECT sum(thuis_uitslag) 
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks 
  WHERE wd <= '$per1_2' AND (uit_wed = t.team_id)) + (

  SELECT sum(uit_uitslag) 
  FROM wed_".$teamnaam_q."_s".$seizoenID."$reeks 
  WHERE wd <= '$per1_2' AND (thuis_wed = t.team_id)))) AS ds 

FROM teams_s".$seizoenID." t 
WHERE klasse_id = ".$klasseID." 
GROUP BY t.naam, t.team_id 
ORDER BY pnt DESC, wd ASC, ds DESC, dlpv DESC, gw DESC

Het is een lange query, maar veel korter zal niet kunnen.

En wat als je overzichten van 10 jaar lang wilt? Voor een losse periode is het uiteraard vrij simpel

Dan is het alleen maar mogelijk als alles (dus alle seizoenen) in 1 tabel staat, zonder al te veel gedoe te hebben.

[ Voor 9% gewijzigd door Verwijderd op 24-05-2003 19:56 ]

[MBV]

Waarom verander je het ontwerp van je database niet? waarschijnlijk scheelt dat best veel in performance.
tabel Wedstrijd
id mediumint(9)
seizoenID int
TeamID int
wd mediumint(9)
datum date
thuis_wed tinyint(4)
uit_wed tinyint(4)
thuis_uitslag smallint(6)
uit_uitslag smallint(6)
verslag text
opmerking text
titel text

Tabel team
teamID int
teamnaam VARCHAR(30)

Tabel Seizoen
SeizoenID int
Seizoennaam VARCHAR(30)

En kan je even vertellen wat je met die queries wil doen? Ik zie geen verband o.i.d. Waarom zou je geen aparte queries gebruiken, en de gegevens die daaruit komen (teamnaam) in de omvattende? Dat is veel overzichtelijker, en zorgt ervoor dat je iets beter fouten kan tracen.

Voor de performance zou ik zeker indexen gebruiken op de gegevens die het meest gezocht worden. Dat leest een stuk sneller voor de database.

Verder wil ik best wel wat verwedden dat die query korter kan: al was het maar door de vele subqueries door een forlus te laten doen

[ Voor 15% gewijzigd door MBV op 24-05-2003 20:36 ]

[Verwijderd]

MBV schreef op 24 May 2003 @ 20:33:
Waarom verander je het ontwerp van je database niet? waarschijnlijk scheelt dat best veel in performance.

--knip--

Zoiets had ik al in gedachte. Ik ga het binnenkort veranderen.

En kan je even vertellen wat je met die queries wil doen? Ik zie geen verband o.i.d.

Wat ik met de queries wil doen ik heel simpel : een ranglijst maken aan de hand van de resultaten in de wedstrijdtabel.

Daarvoor doe ik het volgende :
De namen en id-nummers van de teams ophalen, daarna het aantal gespeelde wedstrijd berekenen door te kijken van hoeveel wedstrijden van een bepaald team een uitslag aanwezig is. Daarna het aantal gewonnen wedstrijd berekenen. Het thuisspelende team heeft een wedstrijd gewonnen zodra het aantal doelpunten van het thuisspelende team hoger is dan dat van het uitspelende team. Tel daarbij van dat team het aantal gewonnen wedsrijden op van de uitwedstrijden en je hebt het totaal aantal gewonnen wedstrijden. Hetzelfde geldt voor de gelijk gespeelde en verloren wedstrijden. Het aantal punten berekenen is niks anders dan het aantal gewonnen wedstrijden * 3 + het aantal gelijk gespeelde wedstrijden.

Het klinkt simpel , maar het lijkt mij de enige manier om dit aan MySQL te vertellen.

Waarom zou je geen aparte queries gebruiken, en de gegevens die daaruit komen (teamnaam) in de omvattende? Dat is veel overzichtelijker, en zorgt ervoor dat je iets beter fouten kan tracen.

Bedoel je een aparte queries voor het aantal gespeelde, gewonnen, gelijk gespeelde, verloren wedstrijden ??

Dat was het allereerste ontwerp. Het leverde nogal wat problemen op met sorteren + als het met 1 query kan in SQL, waarom dan moeilijk doen met PHP....

Voor de performance zou ik zeker indexen gebruiken op de gegevens die het meest gezocht worden. Dat leest een stuk sneller voor de database.

Dat ga ik zeker doen.

Verder wil ik best wel wat verwedden dat die query korter kan: al was het maar door de vele subqueries door een forlus te laten doen

Ik ga binnenkort (over 2 a 3 weken) wat dingen optimaliseren, zoals de tabelstructuur aanpassen en de query aanpassen waar het kan.

[MBV]

Als je de tabelstructuur aanpast kan de query heel wat korter. Ik dacht dat subquery's in mysql niet echt goed werkte (niet dus, vandaar dat je niks kan vinden). Daarom zou ik het als volgt doen:

code:

SELECT team.naam,  sum(team.punten), count(wedstrijd.*), 
FROM team, wedstrijd
        where team.id = wedstrijd.teamID AND
        wedstrijd.seizoenID = $seizoen
GROUP BY wedstrijd.teamid

De puntentelling is best lastig, ook al zou dat waarschijnlijk zonder subquery wel kunnen. Dat werkt heel wat eenvoudiger als je daarvoor een veldje toevoegd. Is niet netjes qua normalisatie, maar wel zo snel qua performance.
Werkt deze code niet, dan zou ik "HAVING SUM(wedstrijd.punten)>0" toevoegen. Volgens mij hoeft dat niet met count etc, maar ik kan me vergissen.

edit:
hoeveel krijg ik nu van je, nu ik de query zoveel korter heb gekregen door een paar simpele optimalisaties?

[ Voor 10% gewijzigd door MBV op 24-05-2003 22:11 ]

[Verwijderd]

MBV schreef op 24 May 2003 @ 22:10:
Als je de tabelstructuur aanpast kan de query heel wat korter. Ik dacht dat subquery's in mysql niet echt goed werkte (niet dus, vandaar dat je niks kan vinden). Daarom zou ik het als volgt doen:

Sinds versie 4.1 werken subselects wel (alhoewel, het lijkt dus nog niet helemaal goed te werken).

edit:
hoeveel krijg ik nu van je, nu ik de query zoveel korter heb gekregen door een paar simpele optimalisaties?

De eeuwige roem

Ik betwijfel of jouw query gaat werken, omdat er geen kolom punten is
Maar goed, ik heb weer genoeg dingen om over na te denken

[MBV]

dacht dat dat standaard in sql zit, en dat ik het zelfs heb gebruikt. het hoefde in dit geval niet altijd, maar was wel handig.
hoe wil je anders dit doen:

Select naam
from speler X, speler Y
where x.naam = y.naam

Zoekt volgens mij spelers op met dezelfde naam.

[ACM]

Verwijderd schreef op 24 May 2003 @ 19:47:
ACM schreef op 24 May 2003 @ 19:27:
Wat voor data wordt er uit de tabellen vewijderd tijdens een schoonmaak ?
Zijn dit vooral spaties en lege regels ??

Er wordt geen data verwijderd. Er wordt overhead verwijderd, zoals records die deleted waren maar nog niet weggegooid van de disk enzo.

De namen van de teams staan in een apparte tabel. In die tabel heeft iedere team een id-nummer, dat id-nummer heeft een relatie met met de kolommen thuis_wed en uit_wed in de wedsrijd-tabel.

Dat zal best, maar zo'n relatie moet je dan niet aangeven door een andere tabelnaam aan te maken, maar door een relatie aan te leggen.

Het is een lange query, maar veel korter zal niet kunnen.

Wat een rare query.
Ik zou zo gauw niet zien hoe je dat korter kan maken, waarschijnlijk door de subselect(s) te verplaatsen naar de from-clause of zoiets en het resultaat ervan te joinen aan je team-tabel en de berekeningen daarop verder te laten werken in het select-deel.

Dan is het alleen maar mogelijk als alles (dus alle seizoenen) in 1 tabel staat, zonder al te veel gedoe te hebben.

Dat was dan ook wel waar ik op doelde...

Owja, nog iets.
Met bovenstaande query betwijfel ik ten zeerste of mysql de complexiteit ervan enigszins goed weg kan optimaliseren. Postgresql wint het juist met lange/complexe queries...

[ Voor 8% gewijzigd door ACM op 25-05-2003 02:31 ]

[Verwijderd]

Ik heb de database structuur aangepast. De tabellen zien er nu zo uit:

Tabel 'teams'

code:

id              int4    
seizoen_id      int4    
team_id     int4 
team_naam   text  
klasse_id   int4
wpim            int4
aanvang     time

met 3 indices

code:

klasse_id_teams_key
team_id_teams_key
team_naam_teams_key

Tabel 'wedstrijden'

code:

id              int4
seizoen_id      int4
klasse_id   int4
wed_id      int4
wd              int4
datum           date
thuis_wed   int4
uit_wed     int4
thuis_uitslag   int4
uit_uitslag     int4
opmerking   text
titel           text
verslag     text

met 8 indices

code:

wedstrijden_datum_key
wedstrijden_id_key
wedstrijden_klasse_id_key
wedstrijden_opmerking_key
wedstrijden_seizoen_id_key
wedstrijden_thuis_wed_key
wedstrijden_uit_wed_key
wedstrijden_wd_key

Alle wedstrijd gegevens staan in de tabel 'wedstrijd', die nu 3600 records bevat. Het is zo een stuk netter dan 41 tabellen met dezelfde structuur en 30, 66 of 132 records per tabel.

Ik dank u voor de tips

[MBV]

Mag ik vragen hoe je query er nu uitziet? m.a.w. hoever zat ik ernaast?

[Verwijderd]

MBV schreef op 25 May 2003 @ 21:42:
Mag ik vragen hoe je query er nu uitziet? m.a.w. hoever zat ik ernaast?

Zover ben ik nog niet gekomen. Alleen de data heb ik overgezet naar de nieuwe tabel. De queries komen een andere keer.

Als het zover is laat ik hier wel ff weten hoe de query eruit ziet

[MBV]

Daar hou ik je aan