SQL lekérdezés gyorsítása
2022-07-15T14:45:10+02:00
2022-07-16T11:35:43+02:00
2022-08-12T09:50:32+02:00
grannie
Sziasztok!

Van egy adatbázisom, ami cikkeket és besorolásokat/kategóriákat tárol.
A cikkek száma kb 150 ezer, a kategóriáké 50.
Egy cikk több kategóriába is tartozhat, ezért azt külön táblában tárolom a fő és alkategória azonosítójával.

Két lekérdezést írtam egy adott alkategóriába tartozó cikkek listázására:

SELECT C.* FROM besorolasok B LEFT JOIN cikkek C ON C.cikkszam = B.cikkszam WHERE B.fokategoria_id = '1' AND B.alkategoria_id = '18' GROUP BY C.modellszam
a fenti lekérdezés kb 5 percig fut, ami horror, másik oldalról is próbáltam megközelíteni:

SELECT C.* FROM cikkek C WHERE (SELECT COUNT(*) FROM besorolasok B WHERE C.cikkszam = B.cikkszam AND B.fokategoria_id = '1' AND B.alkategoria_id = '18') > 0 GROUP BY C.modellszam
ez is hosszú percekig fut, mire kiad eredményt.

A kérdésem az, hogy hogyan lehetne optimalizálni az adattárolást vagy hogyan állítsam össze a lekérdezést, hogy ne kelljen ilyen sokat várni a listázásra?

A segítséget előre is köszi.

Üdv,
grannie
Mutasd a teljes hozzászólást!
Szia,

Először is QXY javaslatának megfelelően explain plant kell nézni, az alapján gondolkodni.

Viszont az első select-tel vannak amúgy is gondok:

SELECT C.* FROM besorolasok B LEFT JOIN cikkek C ...
Csak a CIKKEK tábla tartalma érdekel, mégsem ő a főtábla, hanem a BESOROLASOK. Ráadásul left join-nal kapcsolod, ami tovább lassít, illetve megjelenhetnek csupa NULL sorok.

SELECT C.* FROM [...] GROUP BY C.modellszam
Ez valami MySQL-izmus lehet, hogy megengedi a C.*-ot megjeleníteni úgy, hogy csak egyetlen oszlopra, a modellszam-ra van group by. Mi lesz a többi cella tartalma (értelmesen), ha a modellszámonként összevonja a sorokat? Vagy inkább DISTINCT-et szeretnél, mivel a join miatt többszöröződnek a CIKKEK sorok?

A második lekérdezésed valószínűleg soha nem fog gyorsan futni a subselect miatt.

Egy lehetséges variáció, ami nem többszörözi a CIKKEK sorokat (erre is explain plant kell nézni, hova kell index):

select c.* from cikkek c where exists ( select 1 from besorolasok b where c.cikkszam = b.cikkszam and b.fokategoria_id = '1' and b.alkategoria_id = '18' )
Mutasd a teljes hozzászólást!

  • Futtasd le a lassú lekérdezéseid EXPLAIN-el, majd az eredmény ismeretében indexelj!
    Mutasd a teljes hozzászólást!
  • Szia,

    Először is QXY javaslatának megfelelően explain plant kell nézni, az alapján gondolkodni.

    Viszont az első select-tel vannak amúgy is gondok:

    SELECT C.* FROM besorolasok B LEFT JOIN cikkek C ...
    Csak a CIKKEK tábla tartalma érdekel, mégsem ő a főtábla, hanem a BESOROLASOK. Ráadásul left join-nal kapcsolod, ami tovább lassít, illetve megjelenhetnek csupa NULL sorok.

    SELECT C.* FROM [...] GROUP BY C.modellszam
    Ez valami MySQL-izmus lehet, hogy megengedi a C.*-ot megjeleníteni úgy, hogy csak egyetlen oszlopra, a modellszam-ra van group by. Mi lesz a többi cella tartalma (értelmesen), ha a modellszámonként összevonja a sorokat? Vagy inkább DISTINCT-et szeretnél, mivel a join miatt többszöröződnek a CIKKEK sorok?

    A második lekérdezésed valószínűleg soha nem fog gyorsan futni a subselect miatt.

    Egy lehetséges variáció, ami nem többszörözi a CIKKEK sorokat (erre is explain plant kell nézni, hova kell index):

    select c.* from cikkek c where exists ( select 1 from besorolasok b where c.cikkszam = b.cikkszam and b.fokategoria_id = '1' and b.alkategoria_id = '18' )
    Mutasd a teljes hozzászólást!
  • Szia!

    Az elsőt nézem még csak...

    SELECT C.* FROM besorolasok B LEFT JOIN cikkek C ON C.cikkszam = B.cikkszam WHERE B.fokategoria_id = '1' AND B.alkategoria_id = '18' GROUP BY C.modellszam


    Kérdés, miért kell főkategóriának és alkategóriának is egyeznie?
    Van olyan alkategória ami több főkategóriához is tartozik?
    Indexeknél mindenképp legyen cikkszám index a cikkek táblán
    Mindenképp legyen egy összetett index a besorolások táblánál ami tartalmazza a  cikkszám+ alkategória(+főkategória ha az alkategória nem egyedi, lásd amit írtam fentebb) mezőt.

    Illetve félve kérdezem miért vannak a kategória számok idézőjelben?
    Ugye nem VARCHAR típusú???

    A cikkek táblából miért kell az összes mezőt lekérdezni?
    Nem elég pl a cikkszám és a név?

    Mit takar a modelszám a cikkek táblában? miért kell rá group?
    Egy cikkszám csak egyszer forul elő nem?
    Vagy esetleg a cikkszám táblában egy cikkszám többször is szerepel? (Nem egyedi kulcs??)

    + amit még WittnerIsComing írt a join-ról szintén nézd meg.
    Már csak azért is mert szűrés nélkül úgy ki tutod buktatni hogy melyik cikkszám nincs sehováse kategórizálva
    Mutasd a teljes hozzászólást!
  • Ez valami MySQL-izmus lehet, hogy megengedi a C.*-ot megjeleníteni úgy, hogy csak egyetlen oszlopra, a modellszam-ra van group by. 


    Ezt a 8-ast megelőző MySQL-ek engedik meg. A 8-as már nem.
    Mutasd a teljes hozzászólást!
  • Hali!

    Ezt a 8-ast megelőző MySQL-ek engedik meg. A 8-as már nem.

    Ez így nem teljesen igaz/pontos. A 8-ast megelőző verziók is tilthatták*, illetőleg a 8-as is engedheti az ONLY_FULL_GROUP_BY mód értékétől függően. Ha ez az SQL-mód engedélyezett (mintha 8-astól alapból az lenne, 8-as előtt nem), akkor tiltja (GROUP BY-t minden nem csoportosított oszlop aggregációja nélkül vagy aggregált oszlopokat GROUP BY nélkül, stb.), ha nem engedélyezett, akkor engedi.

    * Engedélyezett ONLY_FULL_GROUP_BY módnál is lehet bizonyos esetekben aggregált oszlop-megjelölés: a nem aggregált oszlopokat egyetlen értékre szűrve.

    Mutasd a teljes hozzászólást!
  • Szia!

    Milyen adatbázis konkrétan?

    A tapasztalatom szerint az nagyon fontos, hogy a szükséges indexek legyenek meg. Tehát amely mezők szerepelnek a where-ben, orderby-ban, groupby záradékban, arra legyen index.

    Továbbá legyen megengedve a db kezelőnek hogy használjon bőven memóriát.

    Bele lehetne még jobban menni, de látatlanban elsőre ezeket nézném meg.

    Szép napot!
    Mutasd a teljes hozzászólást!
  • Gondolom, a cikkek táblának van indexe a cikkszám-ra...
    Sztem nézd meg hogy mi van, ha kiveszed a group -by-t. Ez amúgy itt egyenértékű egy distinct -el ,ha jól értem. De amúgy az mire kell? Egy cikk csak egyszer szerepelhet egy besorolásban, nem:?
    Ha mégis kell valmiért (mondjuk, mert több kategória kombinációjából akarod látni a cikkeket, akkor megpróbálhatod egy allekérdezésben grouppolni a cikkszámokat...:
    select c.* from cikkek c 
     inner join (select distinct b.cikkszam from besorolasok b where b feltételei ) b2 on b2.cikkszam = c.cikkszam)
    Mutasd a teljes hozzászólást!
  • Jelen formában +1 szavazat az EXISTS-re, és indexekre.

    Viszont szerintem cikkszam-ot felejtsd el, legyen cikk_id (mi van, ha adott cikket le kell zárni, mert nevet változtat pl, és újabb érvényes ugyanazon a cikkszam-on menne tovább, de más névvel)

    Picit félreérthető, minek kell egyszerre fokategoria és alkategoria, nem elég az utóbbi? Fokategoria-t ki kellene dobni a besorolasok-ból szerintem.

    Kategória db szám elég kicsi, ez azt jelenti, hogy több cikkszám tartozhat adott kategóriába?
    Ebben az esetben több-több kapcsolat, feloldó tábla kellene.
    Mutasd a teljes hozzászólást!
Tetszett amit olvastál? Szeretnél a jövőben is értesülni a hasonló érdekességekről?
abcd