ugrás a tartalomhoz

Lekérdezés két táblából, limit a másodikra

gex · 2007. Már. 14. (Sze), 14.49
sziasztok!

adott két tábla, csoport (csoport_id, csoport_nev) és elem (elem_id, csoport_id, elem_nev).
szeretnék egy olyan lekérdezést írni, ami visszaadja a csoportokat és a hozzájuk tartozó elemek közül fix darabszámút, mondjuk hármat.

kerestem group by és limit szavakkal, de csak hasonló problémák felvetését találtam, megoldását nem.
végül kipróbáltam a mysql correlated subqueries-t is:

SELECT
    csoport_id, csoport_nev, elem_id, elem_nev
FROM
    csoport c
    LEFT JOIN elem e USING(csoport_id)
WHERE
    elem_id = ANY (SELECT elem_id FROM elem WHERE csoport_id = c.csoport_id ORDER BY elem_nev LIMIT 3)
de erre egy hibát dob, miszerint a mysql még nem támogatja a limit és in/all/any/some subquery kombinációt.

úgyhogy kéne nekem egy kerülő megoldás ha van ilyen.
mysql 5.0.26

u.i: a "lekérem a csoport_id-kat és php-vel összeillesztek sok limites lekérdezést unionnal" -nál szeretnék szebb megoldást ha létezik. :)
‹ PostgreSQL módosítás dátuma több sql parancs összekötése ›
 ■
1

kategóriánként első 3 hír

Hodicska Gergely · 2007. Már. 16. (P), 01.09
Teszt adatok:
CREATE TABLE `cat` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(20) collate latin2_hungarian_ci NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci;
INSERT INTO `cat` VALUES (1,'cat1'),(2,'cat2'),(3,'cat3'),(4,'cat4'),(5,'cat5'),(6,'cat6');

CREATE TABLE `news` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(20) collate latin2_hungarian_ci NOT NULL default '',
  `cat_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci;
INSERT INTO `news` VALUES (1,'cat1.1',1),(2,'cat1.2',1),(3,'cat1.3',1),(4,'cat1.4',1),(5,'cat1.5',1),(6,'cat2.1',2),(7,'cat2,2',2),(8,'cat2.3',2),(9,'cat2.4',2),(10,'cat2.5',2),(11,'cat3.1',3),(12,'cat3.2',3),(13,'cat3.3',3),(14,'cat3.4',3),(15,'cat3.5',3),(16,'cat4.1',4),(17,'cat4.2',4),(18,'cat4.3',4),(19,'cat4.4',4),(20,'cat5.1',5);
Lekérdezés:
SELECT
    *
FROM
    (
        SELECT
            N.*, @rownum := @rownum+1 AS rownum
        FROM
            news N
        ORDER BY
            id DESC
    ) sub
    INNER JOIN
    (
        SELECT
            cat_id, MIN(rownum) AS first_in_cat
        FROM
            (
                SELECT
                    N.*, @rownum2 := @rownum2+1 AS rownum
                FROM
                    news N
                ORDER BY
                    id DESC
            ) sub2sub
        GROUP BY
            cat_id
    ) sub2
    ON sub.cat_id = sub2.cat_id
WHERE
    sub.rownum BETWEEN sub2.first_in_cat AND sub2.first_in_cat + 2
Hatékonyság kérdéskörét majd még meg kell vizsgálni.


Üdv,
Felhő
2

ejha

gex · 2007. Már. 16. (P), 18.14
elsőre fel sem fogtam mit írsz, a segédváltozók (ha így hívják) használata pedig eszembe sem jutott. a dokumentáció nem túl bőbeszédű, de legalább hasznosak a hozzászólások.

te sűrűn használsz ilyen lekérdezéseket? ha igen mennyire erőforrásigényesek?

ja és nagyon köszönöm a részletes példát!
3

konkrét eset

Hodicska Gergely · 2007. Már. 16. (P), 20.21
elsőre fel sem fogtam mit írsz, a segédváltozók (ha így hívják) használata pedig eszembe sem jutott.
Az alap ötlet egyik jóbarátomé, de a megoldás kulcsa, hogy legyen valami Oracle rownum szerű valami az RDBMS-ben, a segédváltozó ezt hivatott megoldani.

te sűrűn használsz ilyen lekérdezéseket? ha igen mennyire erőforrásigényesek?
Hát ez elég változó, összetettebb queryk gyakrabban előfordulnak, mert sok esetben jobb időt lehet elérni, mint egy vagy több egyszerű lekérdezés segítségével. Segédváltozókat nem nagyon, de lehet, hogy csak azért, mert még én sem annyira látom, hogy hol lehetnek hatékonyan segítségemre. A konkrét esetben azért jutott eszembe, mert nem olyan rég kerestem ORDER BY RAND() kiváltására hatékony módszereket, és ott találkoztam ezzel a megoldással.

Hatékonyságról meg általában nem szerintem nehéz bármi okosat mondani, érdemes konkrét esetekben megnézni, hogy mit mutat az EXPLAIN, lemérni a válaszidőket, és aszerint dönteni. Amibe mostanában többször belefutottam: kollégák subquery-s megoldásait akár 10-szeresére sikerült gyorsítani JOIN-t használó formára való átírással.


Üdv,
Felhő
4

másik megoldás

Hodicska Gergely · 2007. Már. 17. (Szo), 02.42
Egy levlistás ismerőstől jött a következő ötletes megoldás:
SELECT
    *
FROM
    news n1
WHERE
    (
        SELECT
            COUNT(*)
        FROM
            news n2
        WHERE
            n2.cat_id = n1.cat_id
            AND n2.id > n1.id
    ) <= 2
Ha lesz egy kis időm, akkor utánanézek, hogy melyik megoldás lehet a hatékonyabb.


Üdv,
Felhő
5

szép

gex · 2007. Már. 20. (K), 23.31
ez - ha nem tévedek - egy vérbeli correlated subquery, és tényleg nagyon trükkös. :]

az sql kérések hatákonyságát hogyan lehet mérni? pusztán logikailag (esetleg explain parancs segítségével) kikövetkeztethető melyik erőforrásigényesebb, vagy inkább ezt is rakjam egy nagy lépésszámú ciklusba és méricskéljem a futási időket, vagy ezek kombinációja?
nem éreztem még szükségét annak, hogy az sql parancsaim hatékonyságát mérjem, tehát nincs is tapasztalatom ilyesmiben.
6

teljesítmény mérés

Hodicska Gergely · 2007. Már. 21. (Sze), 23.22
ez - ha nem tévedek - egy vérbeli correlated subquery, és tényleg nagyon trükkös. :]
Részben trükkös, de nem túl hatékony. 100000 sor esetén az első query 1,3 sec volt ez pedig 116 sec körüli.

az sql kérések hatákonyságát hogyan lehet mérni?
Hát az alap az EXPLAIN, ennek segítségével is elég sok mindent meg lehet tudni, plusz a queryt lefuttatva is egyértelműen kiderülhet, hogy mondjuk két query közül melyik a nyerőbb. De elég sok féle lehetőség van még, én is mostanában kezdek kicsit jobban belemélyedni a témába. Most került bele a hivatalos verzióba (5.0.37) a PROFILE parancs. Ezzel is sok mindent ki lehet deríteni egy queryről, plusz ott a SHOW STATUS, szintén sok információt képes közölni.


Üdv,
Felhő
9

profile

gex · 2007. Már. 22. (Cs), 12.50
jó ez a profile, bookmark is volt róla, már csak frissítenem kéne a mysql-t.

köszi neked is az ötleteket!
7

ab

Dualon · 2007. Már. 22. (Cs), 10.05
A Felhő által említetteken kívül az apache beépített benchmarkját, az ab-t próbáltad már? Nem egy lézerszike, de tanulságos dolgokat tud (és kényelmesebb, mint a ciklusozás).
dualon@alpha-neuron:~$ ab -n1000 -c10 http://localhost/fuss_forrest_fuss.php
(Ahol -n a requestek száma, -c pedig a concurrency level.)

(Szerk.: Link...)
8

még nem próbáltam

gex · 2007. Már. 22. (Cs), 12.39
pedig sokszor meg lett említve. megnéztem, egész jó, köszi az ötletet!
10

változó, sorrend

gex · 2007. Már. 23. (P), 19.08
na végre jutott egy kis időm játszani a lekérdezéseddel, pár aprósággal kiegészíteném, hátha más is belefut.

először a változókkal akadt egy kis problémám, mert nem volt értékük, valahol meg kellett volna adni a kezdő értéket is, erre a doksi egy hozzászólásában (Mark Malakanov - 2006. április 30.) találtam megoldást. (persze ez lehet csak nekem volt újdonság, még nem használtam ilyen változókat.)

másodszor pedig a teszt adatokon tényleg működött a dolog, de az én adataimmal nem került bele mindig három elem a csoportba annak ellenére, hogy volt annyi. a where feltétel lehagyásával rájöttem, hogy ha nem rendezem a csoport szerint is az elemeket (ezt a teszt adatok felvitelénél te eleve megoldottad ;]), akkor nem egymás utáni rownum értékeket kapnak az azonos kategóriába tartozó elemek, így megdől a feltétel és torzul az eredmény.

teszt adatok (amikből nem kapok jó eredményt a lekérdezéseddel):

INSERT INTO `cat` VALUES (1,'cat1'),(2,'cat2'),(3,'cat3'),(4,'cat4');
INSERT INTO news VALUES (1,'cat1.1',1),(2,'cat2.1',2),(3,'cat3.1',3),(4,'cat1.2',1),(5,'cat2.2',2),(6,'cat1.3',1),(7,'cat1.4',1),(8,'cat4.1',4),(9,'cat3.2',3),(10,'cat3.3',3),(11,'cat2.3',2),(12,'cat2.4',2),(13,'cat4.2',4),(14,'cat4.3',4);
módosított lekérdezés:

SELECT
    *
FROM
    /* --- subquery 1 --- */
    (SELECT
        N.*, @rownum:=@rownum+1 AS rownum
    FROM
        (SELECT @rownum:=0) r, /* @rownum inicializálása */
        news N
    ORDER BY
        cat_id, /* rendezés csoport szerint is */
        id DESC
    /* --- subquery 1 --- */
    ) sub
    INNER JOIN
    (SELECT
        cat_id, MIN(rownum) AS first_in_cat
    FROM
        /* --- subquery 2 --- */
        (SELECT
            N.*, @rownum2:=@rownum2+1 AS rownum
        FROM
            (SELECT @rownum2:=0) r, /* @rownum inicializálása */
            news N
        ORDER BY
            cat_id, /* rendezés csoport szerint is */
            id DESC
        /* --- subquery 2 --- */
        ) sub2sub
    GROUP BY
        cat_id
    ) sub2
    ON sub.cat_id = sub2.cat_id
WHERE
    sub.rownum BETWEEN sub2.first_in_cat AND sub2.first_in_cat + 2
ezen kívül bejelöltem egy subquery1 és egy subquery2 részt, ha jól látom ezekbe ugyanaz kell, hogy kerüljön. tehát ha bizonyos feltételek alapján akarom az utolsó n darab hírt megkapni, mind a kettőben módosítanom kell. ha bonyolultabb dolgot akarok (pl: híreket egy-két másik táblával összefűzni), akkor is elég jó lesz a teljesítmény? vagy a két teljesen azonos subquery-t cache-eli a mysql, úgyhogy ez nem lesz számottevő?

köszi, ha végigolvastad. :D
11

re

Hodicska Gergely · 2007. Már. 24. (Szo), 21.58
először a változókkal akadt egy kis problémám, mert nem volt értékük
Ez érdekes, mert én is ezzel a megoldással kezdtem, de aztán kipróbáltam értékadás nélkül (külön sessionben is), és nem volt vele semmi gond.

ha nem rendezem a csoport szerint is az elemeket
Tényleg, jogos. :)

ezen kívül bejelöltem egy subquery1 és egy subquery2 részt
A második subqueryben érdemes lenne N.* helyett N.cat_id-t használni, ezzel elérhető, hogy ne nyúljon a táblához, hanem elég legyen neki az index az adat szükséglethez. Ha szeretnél még plusz táblákat hozzácsatolni, akkor vlaszeg nem az a jó megközelítés, ha mindkét subqueryt módosítod, hanem hogy az első subqueryben sem N.*-t kérsz le, hanem cat_id-t és news_id-t, majd ezt az egész queryt használod egy táblaként, és ehhez joinolod az egyéb táblákat.


Üdv,
Felhő
12

konkrét példa

gex · 2007. Már. 24. (Szo), 23.27
a konkrét feladatban projectek és feladatok vannak, de azon kívül, hogy a csak jelenleg folyamatban lévő (status flag) feladatokat akarom megmutatni, mindenki csak a maga alá tartozó feladatokat láthatja, amihez szükségem van az adott user alá tartozó résztvevők (user és project hierarchikus kapcsolótáblája) listájára is, ezzel joinolom össze a task táblát.
ezt nem tudom úgy megvalósítani, hogy lekérem a projectenkénti x db utoljára létrehozott még folyamatban lévő feladatot és utána nézem meg, hogy az adott user alá tartozik-e.

a változók nekem se phpmyadminban se parancssorban nem mentek inicializálás nélkül.
13

UNION

Hodicska Gergely · 2007. Már. 25. (V), 03.50
a konkrét feladatban projectek és feladatok vannak
Az könnyen előfordulhat még, hogy ha nem nagyon sok projekted van, akkor sokkal jobban jársz, ha projektenként kéred le az adatokat, és ezeket a queryket UNION-nal kötöd össze (ez esetben ugye nem gond az első x lekérdezése, mert használhatod a LIMIT-et). Ki kell próbálni.


Üdv,
Felhő
14

ahol elkezdtem :]

gex · 2007. Már. 25. (V), 08.39
u.i: a "lekérem a csoport_id-kat és php-vel összeillesztek sok limites lekérdezést unionnal" -nál szeretnék szebb megoldást ha létezik. :)

sebaj, jó sokat tanultam így is! ;]
15

weblabor a konkrét példa

gex · 2007. Nov. 14. (Sze), 17.43
múltkor elbambultam a követő előtt ülve és eszembe jutott ez a régi téma. a kérdésem az, hogy a követőt hogyan oldjátok meg? hiszem ott éppen így van megoldva a dolog, minden témánál látszik az utolsó tíz hozzászólás, meg egy link a maradékra.