[MySQL] Query met LEFT JOIN erg langzaam

[Dennis013]

Momenteel ben ik bezig om de database achter mijn weblog om te zetten van Access naar MySQL.
Ik heb gemerkt dat de site erg langzaam wordt van een bepaalde query. (65 seconde volgens phpmyadmin)

code:

SELECT count(commentaar.blog_ID) as AantalReacties  
FROM blog   
LEFT JOIN commentaar ON blog.blog_ID=commentaar.blog_ID  
group by blog.blog_id  ORDER BY blog.blog_id DESC

Deze query zorgt ervoor dat ik per bericht kan laten zien hoeveel reacties er zijn.

Ik heb geen ervaring met MySQL en ben eigenlijk heel verbaasd dat Access deze query wel heel snel uitvoert, maar MySQl niet! In de site zitten nog vier andere query's en die laden bij elkaar in, rond de 4-5 seconden. Nog niet snel.... dus dat wil ik ook heel graag oplossen.

Nou heb ik ook al in de FAQ en op het forum wat gelezen over indexes, maar ik snap het nog niet.
Kan iemand deze beginner een stap in de goede richting geven?

[ Voor 20% gewijzigd door Dennis013 op 27-05-2006 11:45 ]

[hamsteg]

Ook ik had eens een hele trage query, de jusite index zetten loste alles op. Misschien handig als je wat meer verteld over je database structuur ?

[whoami]

Indexen zorgen ervoor dat je sneller kunt zoeken, sorteren en joinen. Zonder index moet de ganse tabel doorlopen worden, als je een index hebt op een bepaald veld, kan het zoeken / sorteren op dat veld een stuk sneller gaan. (Vergelijk het met zoeken in het register van een telefoonboek).
Weet wel dat een index bij een insert/update/delete wel moet bijgewerkt worden, dus dat geeft wat overhead. Maak dus geen onnodige indexen.

Verder vraag ik me af waarom je een left join doet ? Dat is in dit geval toch nergens voor nodig ? Je gaat het aantal reacties tellen, maar een reactie hangt toch altijd aan een artikel, je zal toch geen reacties hebben die niet aan een artikel hangen ? Vervang die left join dus door een inner join (left/right joins zijn trager dan inner joins).

[Dennis013]

Hmm, het is dus een simpele access database waarvan ik de gegevens uiteindelijk in een MySQL-database gekregen heb. Dat was op zich al een hele bevalling. Wel heel veel van geleerd!

Maar goed. Feitelijk is het niks anders dan wat simpele tabellen. Verder niks bijzonders, ja.. met een index (primaire sleutel)

code:

CREATE TABLE `blog` (
  `blog_id` int(10) unsigned NOT NULL auto_increment,
  `blog_titel` varchar(80) default NULL,
  `blog_tekst` text,
  `blog_auteur` varchar(50) default NULL,
  `blog_email` varchar(50) default NULL,
  `datum` varchar(50) default NULL,
  `tijd` varchar(50) default NULL,
  `commentaar` varchar(50) default NULL,
  PRIMARY KEY  (`blog_id`)
) TYPE=MyISAM;
CREATE TABLE `commentaar` (
  `commentaar_id` int(10) unsigned NOT NULL auto_increment,
  `blog_id` int(11) default NULL,
  `commentaar_auteur` varchar(50) default NULL,
  `commentaar_email` varchar(50) default NULL,
  `datum` varchar(50) default NULL,
  `commentaar_tekst` text,
  PRIMARY KEY  (`commentaar_id`)
) TYPE=MyISAM;

[Michali]

Ik zou bij blog_id van tabel commentaar ook een index leggen. Je zult zien dat de query dan al een stuk sneller is.

Zie deze pagina van de handleiding ook.

[ Voor 33% gewijzigd door Michali op 27-05-2006 12:02 ]

[bloody]

Ik zou je ook willen aanraden om de blog_id kolommen van beide tabellen van hetzelfde datatype en grootte te maken.
(één is unsigned 10, ander signed 11)

[Dennis013]

Michali schreef op zaterdag 27 mei 2006 @ 12:01:
Ik zou bij blog_id van tabel commentaar ook een index leggen. Je zult zien dat de query dan al een stuk sneller is.

Zie deze pagina van de handleiding ook.

Wow... Dat werkt al meteen super!

[b][message=25833193,noline]whoami schreef op zaterdag 27 mei 2006 @ 11:54[/message
Verder vraag ik me af waarom je een left join doet ? Dat is in dit geval toch nergens voor nodig ? Je gaat het aantal reacties tellen, maar een reactie hangt toch altijd aan een artikel, je zal toch geen reacties hebben die niet aan een artikel hangen ? Vervang die left join dus door een inner join (left/right joins zijn trager dan inner joins).

Ik heb werkelijk geen idee waarom ik dat gedaan heb. Dat kwam ik waarschijnlijk tegen op w3schools en dat werkte in de oude situatie prima met access.. Verder niet meer over nagedacht.


Update
Ik zie nu in phpmyadmin de volgende melding staan
PRIMARY and INDEX keys should not both be set for column `commentaar_id`

wat betekend dat?

[ Voor 54% gewijzigd door Dennis013 op 27-05-2006 12:12 ]

[Knutselsmurf]

whoami schreef op zaterdag 27 mei 2006 @ 11:54:
Verder vraag ik me af waarom je een left join doet ? Dat is in dit geval toch nergens voor nodig ? Je gaat het aantal reacties tellen, maar een reactie hangt toch altijd aan een artikel, je zal toch geen reacties hebben die niet aan een artikel hangen ? Vervang die left join dus door een inner join (left/right joins zijn trager dan inner joins).

In dit geval is een left join wel nodig. Namelijk voor de gevallen dat er nog geen commentaren zijn. Een inner-join zal dan de blog's niet tonen, terwijl dat met een left-join net jes een count van 0 oplevert.

[Dennis013]

Knutselsmurf schreef op zaterdag 27 mei 2006 @ 12:12:
[...]

In dit geval is een left join wel nodig. Namelijk voor de gevallen dat er nog geen commentaren zijn. Een inner-join zal dan de blog's niet tonen, terwijl dat met een left-join net jes een count van 0 oplevert.

Ja dat is trouwens waar. Dat is inderdaad de reden.

[whoami]

Eh, die query doet enkel een count, en haalt dus ook niets anders op.
Moest het zo zijn dat je een lijst wil van de artikels met hun aantal reacties, dan zou je idd een left join doen:

code:

select blog.naam, count(1)
from blog
left join commentaar on blog.blog_id = commentaar.blog_id 
...
group by blog.naam

maar dat is hier dus niet het geval.

Dennis013: Ik geloof dat je dus een index hebt gelegd op hetzelfde veld als het veld waar je PK al ligt.
Je hebt naast je PK index ook een andere index gelegd op hetzelfde veld.

Nog een vraagje, waarom group je op blog_id ? Group BY hoef je enkel te doen als je in je SELECT clause ook velden staan hebt, die geen aggregate functie zijn.
Zie de query die ik hier eerder in m'n reactie gepost heb; daar groepeer je op naam, omdat dat geen aggregate functie is.
Grouperen op blog_id is in jouw geval zinloos (tenzij het niet de volledige query is die je gepost hebt, maar ook dan nog is het zeer twijfelachtig of je wel goed gebruik maakt van group by).

[ Voor 33% gewijzigd door whoami op 27-05-2006 12:22 ]

[SPee]

Of waarom zet je niet gewoon de blog id ook niet in de select

Wat ik hier zie, is dat je alleen de aantallen ophaalt en ik vrees door die order by, dat je die via je programmatuur koppelt aan een blog id. Niet slim als de lijsten niet overeen kunnen komen (bv via nieuwe insert)

[Dennis013]

@Whoami
Hmm, ik vind het lastig om hier een goed antwoord op te geven. Ik ben echt een beginner op het gebied van SQL en databases.
Ik ben daarom niet bekend met de termen die je gebruikt. 'PK index' zegt me weinig en 'aggregate functie' eigenlijk al helemaal niks.

De SQL statement had ik eens eerder gebruikt bij een ander weblog wat ik gemaakt had, maar dat is al misschien al jaren geleden. Wanneer ik nu 'group by' weg haal krijg ik een server-error omdat de recordset end of field is.

@Spee, die blogid staat toch in de select of zeg ik nu iets geks?
Nu haal ik 2 lijsten op (tekst van het bericht en aantal reacties), die gesorteerd zijn op Blog_id en die repeat ik alle twee over de pagina.
Volgens mij komen ze dan altijd overeen.

[ Voor 30% gewijzigd door Dennis013 op 27-05-2006 12:38 ]

[Michali]

whoami schreef op zaterdag 27 mei 2006 @ 12:18:
Nog een vraagje, waarom group je op blog_id ? Group BY hoef je enkel te doen als je in je SELECT clause ook velden staan hebt, die geen aggregate functie zijn.
Zie de query die ik hier eerder in m'n reactie gepost heb; daar groepeer je op naam, omdat dat geen aggregate functie is.
Grouperen op blog_id is in jouw geval zinloos (tenzij het niet de volledige query is die je gepost hebt, maar ook dan nog is het zeer twijfelachtig of je wel goed gebruik maakt van group by).

Ik zou dan eerder verwachten dat je op commentaar.blog_id zou groeperen. Maar zou dat echt uitmaken? In feite groepeer je op hetzelfde, omdat commentaar.blog_id aan blog.blog_id gelinked wordt. Misschien is MySQL daar wel slim genoeg voor.

[whoami]

Michali schreef op zaterdag 27 mei 2006 @ 12:39:
[...]

Ik zou dan eerder verwachten dat je op commentaar.blog_id zou groeperen.
Maar zou dat echt uitmaken? In feite groepeer je op hetzelfde, omdat commentaar.blog_id aan blog.blog_id gelinked wordt. Misschien is MySQL daar wel slim genoeg voor.

Tja, dat is nu eens het 'slechte' aan MySQL. Die gaat daar gewoon niet goed mee om waardoor mensen denken dat ze hun query goed schrijven.

Probeer deze query maar eens in een DBMS als SQL Server of Oracle:

code:

select naam, count(1)
from tabel
inner join anderetabel on ...
group by id

Dat gaat gewoon niet werken.
Grouperen doe je op de velden die in je select list staan, en geen aggregate functie (min , max, count, etc...) zijn.

[whoami]

Dennis013 schreef op zaterdag 27 mei 2006 @ 12:35:
@Whoami
Hmm, ik vind het lastig om hier een goed antwoord op te geven. Ik ben echt een beginner op het gebied van SQL en databases.
Ik ben daarom niet bekend met de termen die je gebruikt. 'PK index' zegt me weinig en 'aggregate functie' eigenlijk al helemaal niks.

Ik bedoel dat je je PK (primary key) gelegd hebt op een bepaald veld, en nog eens een aparte index gelegd hebt op dat veld. Je hebt dus 2 indexen op eenzelfde veld, wat nutteloos is.

De SQL statement had ik eens eerder gebruikt bij een ander weblog wat ik gemaakt had, maar dat is al misschien al jaren geleden. Wanneer ik nu 'group by' weg haal krijg ik een server-error omdat de recordset end of field is.

Hoe ziet je query er precies uit ? Is dat de query zoals je 'm gepost hebt ?

@Spee, die blogid staat toch in de select of zeg ik nu iets geks?

Ik zie enkel een count staan.

Nu haal ik 2 lijsten op (tekst van het bericht en aantal reacties), die gesorteerd zijn op Blog_id en die repeat ik alle twee over de pagina.
Volgens mij komen ze dan altijd overeen.

Waarom doe je dat niet in één query, zoals ik eerder gepost had ?

code:

select blog.naam, count(1)
from blog
left join commentaar on blog.blog_id = commentaar.blog_id
group by blog.naam

[Verwijderd]

Waarom doe je dat niet in één query, zoals ik eerder gepost had ?

Misschien omdat blog.naam niet bestaat, en omdat niet duidelijk is wat je nou echt aan het counten bent?

Tja, dat is nu eens het 'slechte' aan MySQL. Die gaat daar gewoon niet goed mee om waardoor mensen denken dat ze hun query goed schrijven.

Dat bedoel ik. Met MSSQL zou ik uitkomen op zoiets:

SQL:

select 
  blog_id, 
  blog_titel,
  (select count(*) from commentaar
   where blog_id = blog.blog_id) as comments
from blog
order by 3 desc

Geen idee of dat in MySQL ook werkt overigens...
MySQL users zijn uit noodzaak (in het verleden) heel creatief geworden met joins, maar vaak zijn subqueries handiger en leesbaarder.

[Dennis013]

whoami schreef op zaterdag 27 mei 2006 @ 12:46:
Ik bedoel dat je je PK (primary key) gelegd hebt op een bepaald veld, en nog eens een aparte index gelegd hebt op dat veld. Je hebt dus 2 indexen op eenzelfde veld, wat nutteloos is.

Er is wel een aanzienlijke snelheidswinst behaald nadat ik die extra index toevoegde aan blog_id

Hoe ziet je query er precies uit ? Is dat de query zoals je 'm gepost hebt ?

Ja. Nog wel.

Waarom doe je dat niet in één query, zoals ik eerder gepost had ?

code:

select blog.naam, count(1)
from blog
left join commentaar on blog.blog_id = commentaar.blog_id
group by blog.naam

Goed plan, ik snap ook wat je bedoelt.
Hoe haal ik dan de overige benodigde velden eruit?

Dus:blog.blog_id, blog_titel, blog_tekst, blog_auteur, blog_email

[ Voor 3% gewijzigd door Dennis013 op 27-05-2006 14:34 ]

[whoami]

Misschien omdat blog.naam niet bestaat, en omdat niet duidelijk is wat je nou echt aan het counten bent?

blog_titel dan.
Waarom zou het niet duidelijk zijn wat je aan het counten bent ? Het aantal gereturnede rows wordt geteld, of je daar nu 1 zet (weet niet of dat werkt in mySql), of een veld-naam, dat doet er niet toe.
Met deze query ga je gewoon het aantal rijen tellen waarvoor de naam (titel) hetzelfde is.
Maar, wat wil je nu precies doen eigenlijk ?
Gewoon, alle artikelen die in 'blog' zitten, ophalen, en ook de reacties daarvan ?

Dan kan je, denk ik, beter gewoon dit doen:

Gewoon, alles wat je wil ophalen, ophalen en sorteren op blog_id, en volgorde waarin je de boel wilt tonen.
bv:

code:

select blog.blog_id, blog.blog_titel, blog.datum, commentaar.xxx, comentaar.yyy , ...
from blog
left join commentaar on blog.blog_id = commentaar.blog_id
order by blog.datum, blog.blog_id, commentaar.datum

Met deze query heb je gewoon alles wat je wil tonen, opgehaald. Dan is het de taak van je 'client-code' (php bv, als je daarmee werkt), om alles goed te tonen.
Je overloopt de resultset, op deze manier:
_pseudo-code

code:

while( recordSet.EOF == false )
{
    int currentBlogId = recordSet.BlogId;
  
    echo recordSet.artikel_naam
    ...
    int aantalReacties = 0;
    while( recordSet.EOF == false && currentBlogId = recordSet.BlogId )
    {
          // haal hier het commentaar op
          aantalReacties++;
          recordSet.MoveNext();
    }    
    // Op dit moment weet je hoeveel reacties het artikel heeft, en heb je ook de reacties opgehaald.
}

Ik zie trouwens dat je je datum in een varchar veld zet; wat is de reden daarachter ? Daarmee ga je vroeg of laat in de problemen komen; gebruik gewoon een datetime veld.

[Verwijderd]

Waarom zou het niet duidelijk zijn wat je aan het counten bent ? Het aantal gereturnede rows wordt geteld, of je daar nu 1 zet (weet niet of dat werkt in mySql), of een veld-naam, dat doet er niet toe.

Of 't nu count(1), count(veldnaam) of count(*) is maakt mij ook niet uit, en de meeste databases ook niet, maar in jouw query is niet direct duidelijk wat je telt: records in de blog tabel of in de commentaar tabel.
Vandaar mijn opmerking dat een subquery vaak leesbaarder is, ook al doet 'ie precies hetzelfde.

Hoe haal ik dan de overige benodigde velden eruit?

Uitgaande van whoami's query kom je dan op iets als:

SQL:

select 
  blog_id,                                               
  min(blog_titel) as blog_titel, 
  min(blog_tekst) as blog_tekst, 
  min(blog_auteur) as blog_auteur, 
  min(blog_email) as blog_email,
  count(1) as comments
from blog
left join commentaar on blog.blog_id = commentaar.blog_id
group by blog_id

Let niet op die min() dingen. Jij en ik weten dat bij iedere blog_id maar 1 tekst, auteur, email, e.d. hoort, maar je database weet dat niet, en verwacht in een 'group by' query alleen velden die in de where-clause staan, of geagregeerde velden (min, max, sum, avg, count).
Ik heb ondertussen begrepen dat je bij MySQL die min() dingen ook weg kunt laten, maar m.i. ben je dan de SQL standaard aan het verkrachten.

Met een subquery (niet elke MySQL is daar blij mee) krijg je iets als:

SQL:

select  
  blog_id,  
  blog_titel,  
  blog_tekst,
  blog_auteur,
  blog_email,
  (select count(*) from commentaar 
   where blog_id = blog.blog_id) as comments 
from blog 
order by 6 desc

[whoami]

Verwijderd schreef op zaterdag 27 mei 2006 @ 20:36:
[...]
Of 't nu count(1), count(veldnaam) of count(*) is maakt mij ook niet uit, en de meeste databases ook niet, maar in jouw query is niet direct duidelijk wat je telt: records in de blog tabel of in de commentaar tabel.

Dat doet er ook niet toe, want je joined.

Als je in de blog tabel 2 records hebt, en in de commentaar tabel heb je voor het eerste record in de blog tabel 3 records, en 4 records voor het andere record in de blog tabel, dan zal je dit krijgen als resultaat:

code:

blog-record 1     3
blog-record 2     4

Je hebt nl. de 2 tabellen gejoined; dan spreek je over een resultset, en niet meer echt over 'records in tabel x of y'. Als je die count weglaat zal je dus 7 records terugkrijgen, die groupeer je per artikel-naam.

Vandaar mijn opmerking dat een subquery vaak leesbaarder is, ook al doet 'ie precies hetzelfde.

En ook even efficient ? Hoe zien de execution plans eruit ? Wellicht dat een beetje DBMS wel tot eenzelfde execution plan komt, maar toch...
Een subquery in je select list zal per record uitgevoerd worden.

[Verwijderd]

Een subquery in je select list zal per record uitgevoerd worden.

En een aggregate uit een andere tabel die gejoined is met je hoofdtabel moet ook per record uitgevoerd worden. Same difference...

[ Voor 22% gewijzigd door Verwijderd op 27-05-2006 21:55 ]