MySQL LIMIT in spremenljivke

October 13, 2007 2 minute read

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.

Vini