Google+ Followers

woensdag 28 december 2011

Beperk je LIMIT

LIMIT en z'n ega OFFSET worden gebruikt om een resultset te beperken tot alleen een interessant plakje van de totale uitkomst. De pest is dat de database pas kan bepalen welke records binnen de opgegeven OFFSET en LIMIT vallen als hij de hele resultset ter beschikking heeft.

Dus als je een query hebt die een beste borrel werk doet om dingen te tellen en te sorteren, dan gaat de database eerst al die berekeningen doen en de uitkomst sorteren, om vervolgens alleen de gevraagde plak terug te geven.

Om dit soort queries sneller te maken kun je de manier van werken licht veranderen door de LIMIT en OFFSET alleen toe te passen op dat deel van de brondata waar hij ook echt op wordt toegepast.

Stel je wilt een lijst van klanten met de totale waarde van hun bestellingen, hun totale aantal transacties, gemiddelde bestelwaarde etc etc, gesorteerd op hun naam, beginnend op klant 15 in de lijst. Gewoonlijk zou je dan de usertabel raadplegen en die joinen aan diverse andere tabellen en/of subqueries:



SELECT *
FROM users
LEFT JOIN (SELECT COUNT(*) FROM transactions GROUP BY userid) AS transactioncounter
ON transactioncounter.userid=users.userid
ORDER BY user.name
LIMIT 15
OFFSET 15;

Nu wordt het aantal transacties voor *alle* users geteld en daar wordt een subset van 15 uit geplukt.

Door de werkvolgorde aan te passen wordt het aantal bewerkingen veel kleiner:




SELECT *
FROM (SELECT * FROM users ORDER BY users.name OFFSET 15 LIMIT 15) AS users
LEFT JOIN (SELECT COUNT(*) FROM transactions GROUP BY userid) AS transactioncounter
ON transactioncounter.userid=users.userid;