Op deze website gebruiken we cookies om content en advertenties te personaliseren, om functies voor social media te bieden en om ons websiteverkeer te analyseren. Ook delen we informatie over uw gebruik van onze site met onze partners voor social media, adverteren en analyse. Deze partners kunnen deze gegevens combineren met andere informatie die u aan ze heeft verstrekt of die ze hebben verzameld op basis van uw gebruik van hun services. Meer informatie.

Akkoord

Vraag & Antwoord

Webprogrammeren & scripting

MySQL query tbv pagination

marientje
9 antwoorden
  • Ik heb hier een stuk code van een voorganger waar ik niet helemaal tevreden mee ben. Wat is het geval?
    In een intranet-omgeving kunnen gegevens van klanten opgevraagd worden. Die gegevens zijn opgeslagen in een diverse tabellen, middels (LEFT) JOIN wordt daar een query op uitgevoerd. Het totaal levert nu zo'n 400.000 records op. Alle resultaten worden in een HTML-tabel getoond aan de gebruiker met een aantal bewerkingsknoppen erachter.

    Nu wil je natuurlijk niet 400.000 regels printen op het scherm, dus is er een pagination toegevoegd. Middels mysql_num_rows() worden de resultaten geteld en wordt de pagination opgebouwd. Er worden 20 rijen geprint op het scherm. Vervolgens werd er nóg een query uitgevoerd, maar dan met een limit erop.

    Nu heb ik al 2 dingen aangepast:
      [*:b9eab3a6b2]Een ontbrekende index voor een JOIN aangemaakt. [*:b9eab3a6b2]De eerste query aangepast naar een SELECT COUNT(*) en dat resultaat gebruiken, daarna dat resultaat gebruiken om de pagination op te bouwen.[/list:o:b9eab3a6b2]Dat scheelt me al ongeveer eenderde in de tijd voor de querys. Echter: -hou je vast- het duurt nog steeds 8 (!) seconden om dit uit te voeren. Nu zijn het natuurlijk enorme tabellen en daarop queryen duurt nu eenmaal lang. Maar toch heb ik het idee dat hier nog veel winst te behalen valt. Sowieso wil ik nog over gaan stappen op de mysqli extensie om e.e.a. te optimaliseren en de database zelf opschonen, maar aan beide kleeft momenteel nog veel meer vast wat ik in een groter verband moet oplossen. Heeft iemand nog tips om dit query-gewijs of PHP-gewijs te optimaliseren? Ik vind 8 seconden namelijk nog steeds idioot lang.
  • Wat weet je van de 20 rijen die je wil hebben? Als je bijvoorbeeld de laatste 20 rijen wil laten zien kan je de laatste id opvragen (pk?) en deze in de voorwaarde verwerken, dus iets van "WHERE id BETWEEN getal - 20 AND getal". Op die manier worden alle nutteloze joins niet uitgevoerd en bespaar je daar op de rekenkracht. Niet getest, dus dit is puur theoretisch en ik zou er dus naast kunnen zitten.

    - Bas
  • Dat zou inderdaad een hoop schelen, maar de data is daar volgens mij niet consistent genoeg voor. Ter indicatie: de PK begint netjes bij 1, maar loopt tot anderhalf miljoen voor de hoogste. Terwijl ik maar 400.000 records heb. Ik heb ze nog niet exact uitgeplozen, maar ergens tussen de 200.000 en 300.000 zit een enorm gat. Geen idee waar dat vandaan komt en of daar iets aan logica in zit.
  • Desalniettemin is die lijst bekend en kan je die opvragen met een hele simpele query en sorteren op grootte, eventueel in combinatie met een boven/onderlimiet en een "limit" om het aantal getallen dat je terugkrijgt te beperken. Vervolgens kan je de bovenstaande aanpak proberen door óf met PHP er een [i:e86dfe9fba]comma seperated list[/i:e86dfe9fba] van te maken óf door het resultaat via een subquery direct aan de bovenliggende query te voeren, waarschijnlijk met "WHERE id IN (subquery)". Moet prima te doen zijn.

    - Bas
  • Op die manier had ik het nog niet bekeken, bedankt. Ik ga er eens mee stoeien. :)
  • Hmm… ik keek hier toch even de verkeerde kant op. Hiermee kan ik uiteindelijke de tweede query optimaliseren, maar dat is eigenlijk niet direct nodig*. De meeste lag komt van de query met SELECT COUNT. Die duurt ongeveer 75x langer dan de tweede query.

    *)Kan zeker geen kwaad, maar is niet de grote boosdoener.
  • Maar een simpele SELECT COUNT(*) mag toch niet zo lang duren? Of doe je aan grouping en joins in die select, dan snap ik dat het lang duurt. Helpt het als je alleen de pk telt? Heb je al gekeken met EXPLAIN?

    - Bas
  • De query is nu als volgt:
    [code:1:72ebeda008]SELECT
    COUNT(*)
    FROM
    `Codes`
    JOIN
    `Project` ON `OrderId` = `Project`.`Id`
    LEFT JOIN
    `consument` ON `Codes`.`id` = `consument`.`code_id`
    JOIN
    `klanten` ON `Project`.`KLantId` = `klanten`.`id`
    LEFT JOIN
    `Adres` ON `Codes`.`Code` = `Adres`.`Code`
    WHERE
    1;[/code:1:72ebeda008]
    Ik kan niet een simpele SELECT COUNT(*) op alleen Codes doen, want dat zijn er meer (scheelt zo'n 20.000 records). Dat zou wel een enorm verschil maken, dan ben ik met een tiende seconde klaar. Er is ook geen andere tabel waarbij ik het een een primary key kan hangen, aangezien dat meer 'eigenschappen' van de Codes zijn.

    Overigens heb ik deze query nu op ± 3 seconden. Het is dus al een hele verbetering.
  • Ik heb even een shortcut genomen. Ik query nu alleen op actieve records van dit jaar, dat is voor 99% van de gevallen voldoende en brengt de totale tijd (voor de queries samen + het parsen) op nog geen 2 seconden. Voor het moment is dat prima, en dat geeft me tijd om e.e.a. even in een groter kader te optimaliseren.

Beantwoord deze vraag

Dit is een gearchiveerde pagina. Antwoorden is niet meer mogelijk.