[MySQL] Slome query?

[Saven]

Hey Tweakers,
Sorry Maar alweer even een topic van mij in dit forum.

Ik heb een vervelend probleempje, namelijk dat een query die ik uitvoer 0,15 seconden ongeveer kost.
Dat komt door de LEFT JOIN blijkbaar die ik gebruik:

PHP:

    private function __get_news($limit)
    {
        $query = $this->core->db->query
        ('
            SELECT
                n.id, n.tijd, n.gesloten, n.titel, n.bericht, n.avatar, COUNT(r.id) as reacties
            FROM
                nieuws n
            LEFT JOIN
                nieuwsreacties r ON (r.nid = n.id)
            GROUP BY
                n.id
            ORDER BY
                n.id DESC
            LIMIT
                '.$limit.'
        ');
        
        if( $query->rowCount() == 0 )
        {
            return false;
        }
        else
        {
            return $query->fetchAll(PDO::FETCH_ASSOC);
        }
    }

Nu moet ik wel LEFT JOIN volgensmij gebruiken omdat ik ook wil dat hij nieuwsreacties laat zien waar nog geen reacties op zijn geweest. Met INNER JOIN en RIGHT JOIN gaat dat dus niet lukken, terwijl die 2 joins aanmerkelijk sneller zijn (zo'n 0,03 seconden).

Kan iemand mij misschien uitleggen wat ik nu het beste kan doen? Een parsetime van 0,15 seconden voor alleen het nieuws opvragen is mij een beetje te veel van het goede namenlijk.

Alvast bedankt

[RAJH]

Draai die query eens in PHPMyAdmin met voor de query EXPLAIN. Dan krijg je een mooi overzicht terug met daarin informatie over je query.

[ Voor 3% gewijzigd door RAJH op 24-04-2008 14:13 ]

[Pete]

Naast dat ik je adviseer al je niet geaggregeerde kolommen op te nemen in je Group By is er hier 1 ding van belang: indexen

Doe eens een Explain en kijk wat eruit komt

[AtleX]

Ik gok je je indexes eens moet controleren. Haal je query eens door EXPLAIN en (vanaf MySQL 5.0.37) door de profiler.

[Saven]

Dit komt er uit met EXPLAIN:


Heb er niet zo veel verstand van
Verbaast me trouwens wel dat als ik de query uitvoer in phpmyadmin zelf (zonder explain) dat hij dan wel snel gaat..

[ Voor 5% gewijzigd door Saven op 24-04-2008 14:21 ]

[RAJH]

Zou je voor de volledigheid ook nog even je database schema willen posten? En dat de query opeens wel snel gaat komt waarschijnlijk door de caching functies van MySQL.

[ Voor 38% gewijzigd door RAJH op 24-04-2008 14:28 ]

[AtleX]

Gooi op r.nid en n.id eens een index?

[Saven]

AtleX dankjewel! En de rest ook natuurlijk Index erop gooien heeft idd enorm veel effect .
Zou je misschien uit kunnen leggen wat het nut is van de de Index dan? Want dan zal dit vast en zeker ook wel bij overige querys helpen

Thnx :)!

[JortK]

LEFT OUTER JOIN wil je query nog wel eens wat trager maken.

Leg eens een index aan op r.nid en n.id zoals AtleX ook al zegt.

En waarom trouwens die ORDER BY?

Sorteren van je data doe je toch niet al op database niveau, dit kun je toch in je PHP code doen?

[Morax]

Waren die velden geen Primaty Keys van je tabel dan ? Normaal gesproken is een primary key automatisch al een index namelijk....

JortK schreef op donderdag 24 april 2008 @ 14:28:
Sorteren van je data doe je toch niet al op database niveau, dit kun je toch in je PHP code doen?

Waarom niet? Je DB is prima geschikt om te sorteren hoor, of dacht je dat die order by er voor de gein in zat ?

[ Voor 55% gewijzigd door Morax op 24-04-2008 14:30 ]

[JortK]

Saven schreef op donderdag 24 april 2008 @ 14:27:
AtleX dankjewel! En de rest ook natuurlijk Index erop gooien heeft idd enorm veel effect .
Zou je misschien uit kunnen leggen wat het nut is van de de Index dan? Want dan zal dit vast en zeker ook wel bij overige querys helpen

Thnx :)!

Dit is je perfecte startpunt om daar de voordelen van in te zien en te zien hoe MySQL ermee omgaat

[JortK]

Morax schreef op donderdag 24 april 2008 @ 14:29:
Waarom niet? Je DB is prima geschikt om te sorteren hoor, of dacht je dat die order by er voor de gein in zat ?

Hij zal er vast een reden voor hebben, maar ik persoonlijk sorteer de data nooit op database niveau, maar dat ligt maar net aan de situatie waar TS het voor gebruikt

[Saven]

Morax schreef op donderdag 24 april 2008 @ 14:29:
Waren die velden geen Primaty Keys van je tabel dan ? Normaal gesproken is een primary key automatisch al een index namelijk....


[...]


Waarom niet? Je DB is prima geschikt om te sorteren hoor, of dacht je dat die order by er voor de gein in zat ?

Ze waren wel primary keys natuurlijk, maar geen index blijkbaar

JortK ook dankjewel

Iedereen bedankt voor hun hulp

[mcdronkz]

Morax schreef op donderdag 24 april 2008 @ 14:29:
[...]


Waarom niet? Je DB is prima geschikt om te sorteren hoor, of dacht je dat die order by er voor de gein in zat ?

Nou ja, als je zo gaat redeneren kun je met de huidige functionaliteit in MySQL je halve PHP code opdoeken . Maar inderdaad, sorteren is een klus die je mooi met de database-functionaliteit kunt doen, kun je ook meteen op meerdere kolommen sorteren zonder er al te veel bij hoeven na te denken.

[rrrandy]

JortK schreef op donderdag 24 april 2008 @ 14:28:
LEFT OUTER JOIN wil je query nog wel eens wat trager maken.

Leg eens een index aan op r.nid en n.id zoals AtleX ook al zegt.

En waarom trouwens die ORDER BY?

Sorteren van je data doe je toch niet al op database niveau, dit kun je toch in je PHP code doen?

Als hij in zijn PHP code gaat sorteren krijgt hij een ander resultaat...

[Niemand_Anders]

Saven schreef op donderdag 24 april 2008 @ 14:32:
[...]
Ze waren wel primary keys natuurlijk, maar geen index blijkbaar

Waarschijnlijk zat er geen index op op nid uit de nieuwsreacties tabel. nid is hier geen primary key. nid is alleen een primary key in de nieuws tabel.

[Koppensneller]

JortK schreef op donderdag 24 april 2008 @ 14:28:
LEFT OUTER JOIN wil je query nog wel eens wat trager maken.

Leg eens een index aan op r.nid en n.id zoals AtleX ook al zegt.

En waarom trouwens die ORDER BY?

Sorteren van je data doe je toch niet al op database niveau, dit kun je toch in je PHP code doen?

En waarom zou je dat niet in de je DB willen doen?

edit:
Spuit 11, note to self: een tab niet 10 minuten open laten staan

[ Voor 8% gewijzigd door Koppensneller op 24-04-2008 14:43 ]

[Morax]

mcdronkz schreef op donderdag 24 april 2008 @ 14:32:
[...]


Nou ja, als je zo gaat redeneren kun je met de huidige functionaliteit in MySQL je halve PHP code opdoeken . Maar inderdaad, sorteren is een klus die je mooi met de database-functionaliteit kunt doen, kun je ook meteen op meerdere kolommen sorteren zonder er al te veel bij hoeven na te denken.

Ik geef toe dat het argument van die ORDER BY nergens op slaat, maar je DB is ten eerste gewoon zeer geschikt om te sorteren, en ten tweede moet je de DB soms wel laten sorteren, omdat je anders gewoon niet het resultaat krijgt dat je zou willen.

Hoe haal je anders de laatste 5 items op uit een tabel bijvoorbeeld, zonder die data te sorteren in je query ?

[mcdronkz]

Morax schreef op donderdag 24 april 2008 @ 14:45:
[...]


Ik geef toe dat het argument van die ORDER BY nergens op slaat, maar je DB is ten eerste gewoon zeer geschikt om te sorteren, en ten tweede moet je de DB soms wel laten sorteren, omdat je anders gewoon niet het resultaat krijgt dat je zou willen.

Hoe haal je anders de laatste 5 items op uit een tabel bijvoorbeeld, zonder die data te sorteren in je query ?

Dan moet je eerst gaan tellen hoeveel items er in je resultset zitten, en dan in de loop waarin ze weergegeven worden een check uitvoeren of je wel met de laatste 5 items te maken hebt ofzo. Dat is dus vrijwel onmogelijk. Helemaal als ze schots en scheef door elkaar staan.

Nee hoor, gewoon lekker sorteren in je DB, why not.

- edit -

Nou ja, onmogelijk is een groot woord, maar het is niet de weg die je wilt bewandelen denk ik.

[ Voor 6% gewijzigd door mcdronkz op 24-04-2008 14:56 ]

[BalusC]

En daarnaast kan een zichzelf respecterend relationele database de data ontegenzeglijk véél efficienter sorteren dan een of ander functie in een willekeurig programmeertaal.

[ Voor 3% gewijzigd door BalusC op 24-04-2008 14:57 ]

[whoami]

[b][message=29973729,noline]
En waarom trouwens die ORDER BY?

Sorteren van je data doe je toch niet al op database niveau, dit kun je toch in je PHP code doen?

waarom ?
Waarom zou je de DB niet laten doen waar ze goed in is ? Ga jij maar ff 100.000 rows gaan sorteren in php. Welke sort implementatie ga je daarvoor gebruiken ? Wat is het voordeel om je sortering in php te doen ?

[ Voor 23% gewijzigd door whoami op 24-04-2008 16:07 ]

[GX]

JortK schreef op donderdag 24 april 2008 @ 14:31:
[...]
Hij zal er vast een reden voor hebben, maar ik persoonlijk sorteer de data nooit op database niveau, maar dat ligt maar net aan de situatie waar TS het voor gebruikt

Dat is leuk met resultsets van miljoenen records, om die daarna nog even in je code te gaan sorteren. Hopen dat je server voldoende geheugen heeft om dat 2 keer tegelijk te doen. Of ga je het zelf wegswappen?

Volgens mij ga je precies de verkeerde kant op door het niet in de database op te lossen.

[!null]

Sorteren is iets wat bij de 'Datalaag' hoort. Je zou het eventueel ook als weergave kunnen zien, maar sortering hangt vaak samen met het maken van een bepaalde selectie. En tja, de performance is al reden genoeg.

[PainkillA]

een count kan ook nogal zwaar meetellen op grote hoeveelheden. Kan je mischien beter naderhand met php doen. Ook is een order by vertragend. Verder als die id's waarop je joined primary key zijn heeft het geen extra nut om daar indexes op te leggen. Dit gebeurt al automatisch.

[Sebazzz]

Even iets wat niet met SQL te maken heeft aar met de code, als dat mag: PHP.net raadt het af om methodes met twee underscores te laten beginnen (__) omdat dat gereserveerd is voor Zend framework en PHP methodes. Het werkt nu misschien, maar misschien in de toekomst niet

[paulh]

PainkillA schreef op donderdag 24 april 2008 @ 19:09:
een count kan ook nogal zwaar meetellen op grote hoeveelheden. Kan je mischien beter naderhand met php doen. Ook is een order by vertragend. Verder als die id's waarop je joined primary key zijn heeft het geen extra nut om daar indexes op te leggen. Dit gebeurt al automatisch.

Ja, alleen een beetje jammer dat je in mysql niet kan aangeven of een index ascending of descending gesorteerd moet zijn. Is nu altijd ascending.

[dik_voormekaar]

paulh schreef op donderdag 24 april 2008 @ 20:17:
[...]
Ja, alleen een beetje jammer dat je in mysql niet kan aangeven of een index ascending of descending gesorteerd moet zijn. Is nu altijd ascending.

Is ook niet nodig. Je kunt gewoon "order by ... desc" geven. Die maakt gewoon gebruik van de key die asccending gesorteerd is.

[Verwijderd]

paulh schreef op donderdag 24 april 2008 @ 20:17:
Ja, alleen een beetje jammer dat je in mysql niet kan aangeven of een index ascending of descending gesorteerd moet zijn. Is nu altijd ascending.

Descending indices heb ik na m'n Clipper-tijd nooit meer gezien of nodig gehad. Een beetje RDBMS weet echt wel of 'ie een ascending index van voor naar achter of andersom moet gebruiken...

[Confusion]

Sidenote: als je performance van queries aan het testen ben, kan je zorgen dat de query niet gecached wordt door direct na de SELECT het keyword 'query_no_cache' zie onder bij AtleX te zetten.

Verwijderd schreef op donderdag 24 april 2008 @ 22:46:
Descending indices heb ik na m'n Clipper-tijd nooit meer gezien of nodig gehad. Een beetje RDBMS weet echt wel of 'ie een ascending index van voor naar achter of andersom moet gebruiken...

Je RDBMS kan niet raden welke queries je er op los gaat laten en kan dus niet op voorhand de index optimaliseren voor die queries. In DB2 kan je daarom expliciet aangeven in welke volgorde de index gesorteerd moet zijn. Ik heb nog niet gehoord van een systeem dat adaptief zijn indices aanpast aan de queries die er het meest op worden losgelaten.

[AtleX]

Confusion schreef op vrijdag 25 april 2008 @ 19:48:
Sidenote: als je performance van queries aan het testen ben, kan je zorgen dat de query niet gecached wordt door direct na de SELECT het keyword 'query_no_cache' te zetten.

Kleine correctie. Het is "SQL_NO_CACHE", bijvoorbeeld:

SQL:

SELECT SQL_NO_CACHE FROM users;