SQL

MySQL LIMIT in spremenljivke

sobota, 13. oktober 2007 rodi se Sacha Baron Cohen (1971)

Trenutno se ukvarjam s popolno predelavo nekega velikega novičarskega portala. Na prvi strani je kar nekaj sekcij, ki morajo prikazovati zadnje novice iz izbranih kategorij. Ker je člankov v bazi trenutno okoli 180.000, bi rešitev v stilu SELECT * FROM clanki ORDER BY datum DESC ne bila prav idealna. Na pomoč seveda priskočijo materialized views, se pa kmalu pojavi problem. Za vsako sekcijo moram izbrati kategorije in število člankov, ki jih vsebuje. Ob vsakem dodajanju, spremembi ali brisanju članka, bom izvedel trigger, ki bo preveril, če je potrebno katerega of materializiranih viewjev na novo kreirati. Po potrebi se bo izvedla naslednja procedura:

CREATE PROCEDURE find_grouped(
IN cat_id INT,
IN cat_limit INT
)
BEGIN
DELETE
FROM
cat_articles
WHERE
category_id = cat_id;
INSERT
INTO
cat_articles
(category_id, article_id)
SELECT
cat_id, articles.id
FROM
articles
WHERE
articles.category_id = cat_id
ORDER BY
articles.datum DESC
LIMIT
cat_limit;
END;

Proceduro sem poenostavil, dejansko stanje je namreč takšno, da so lahko v eni sekciji članki iz večih kategorij, nastavitve, katere kategorije vključiti in koliko člankov zajeti, se prebere iz nekaj tabel z nastavitvami in ne podaja v parametrih procedure, kot v našem primeru. Ravno tako se, zaradi optimizacije poizvedbe, iz nastavitev prebere tudi, za koliko dni nazaj objavljenih člankov naj se v poizvedbi analizira. Te stvari sicer za naš problem niso pomembne, ker pa zelo zakomplicirajo poizvedbo, sem jih, zaradi lažjega razumevanja, iz procedure brez slabe vesti izpustil.

No, kje se pojavi problem? Problem je LIMIT, ki ne sprejema spremenljivk, kasnejše branje manuala to tudi potrdi:

User variables may be used in contexts where expressions are allowed. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

Super, kaj pa zdaj? Stric Google ve vse, poglejmo, če je kdo že rešil problem. So ga, na kar nekaj načinov. Eden od njih je uporaba prepared statements, drugega bom pa v nadaljevanju opisal. Ta rešitev se mi je zdela sicer lepša kot prva, pa še vseeno kar grda. Ampak deluje, jebiga.

CREATE PROCEDURE find_grouped(
IN cat_id INT,
IN cat_limit INT
)
BEGIN
DELETE
FROM
cat_articles
WHERE
category_id = cat_id;
SET @__sequence = 0;
INSERT
INTO
cat_articles
(category_id, article_id)
SELECT
cat_id, id
FROM
(
SELECT
(@__sequence:=@__sequence+1) AS rownum,
articles.id
FROM
articles
WHERE
articles.category_id = cat_id
ORDER BY
articles.datum DESC
) AS articles
WHERE
rownum <= cat_limit;
END;

Kaj smo naredili? Umetno smo ustvarili nek row number counter in rezultat omejili le na prvih cat_limit vrstic. Grda ali ne tako zelo grda rešitev? Mogoče celo lepa? Presodite sami. Je pa zaenkrat, po mojem mnenju, še najboljši workaround za omenjeni problem.

Pozna kdo boljšega in lepšega? Vesel ga bom, ponoči namreč zaradi tega slabo spim.

1.
14. oktober 2007, 12:17

Superca, res hvala za to, veliko krat sem študiral pa pacal, proti moji pocariji je tole prav lepo. Res thx.

2.
vasija
14. oktober 2007, 18:31

cool

3.
17. oktober 2007, 10:48

Vini, nič ne štekam, ampak zihr je kul!

P.S.: Za ker novičkarski portal pa gre :D

4.
17. oktober 2007, 12:38

Davorin, vsekakor je kul :) Gre za nek italijanski novičarski portal z borznimi informacijami, ki sicer obstaja že od leta 2000 in deluje na tehnologiji, ki sem jo sprogramiral takrat. Po toliko letih je počasi že čas, da zadeve temeljito posodobimo, se bom pa seveda z njim pohvalil, ko zadevo zaključim, le spremljaj moj blog :)

5.
6. november 2007, 22:20

Vini, pošteno! =) Thank God, da svoje SQL stavke še vedno uspem pisati v "eni vrstici" ... ;-) :shy:

Zakaj pa ne narediš neko opcijo v smislu, da se zadnje novice preprosto ob submitu nekam keširajo? LP.