MySQL lekérdezés gyorsítás

MySQL lekérdezés gyorsítás
2022-05-13T13:46:05+02:00
2022-05-15T20:03:46+02:00
2022-10-15T21:20:40+02:00
vandammee
Sziasztok!

Van egy USER adattáblám kb. 20000 rekorddal.
És van egy BIRALAT adattáblám 31000 rekorddal.

A USER és BIRALAT tablak közti kapcsolatot a user adattablában az azonosito varchar(40), a BIRALAT adattáblában a biraloazonosito varchar(40) mezők teremtik meg.
A user adattáblán van egy index az azonosito mezőn, a biralat táblán pedig van egy index a biraloazonosito mezőn.

Le szeretném kérdezni a user táblából azokat az embereket, aki nem bíráltak, azaz a bírálat táblában egyik rekordnál sem az ő azonosítójuk szerepel a biraloazonosito mezőben.

Erre a következő lekérdezést írtam:

select azonosito from user where azonosito not in (select distinct(biraloazonosito) from biralat)
Namost ez a lekérdezés a fent említett rekordszámok mellett 2perc 10 másodperc alatt fut le.
Azt mondjuk nem értem, hogy a phpmysql, amiben a lekérdezést futtatom, miért azt írja, hogy "Query took 0.2170 seconds."

Kipróbáltam egy másik féle módon is ezt a lekérdezést:

select azonosito from user as U where not exists (select biraloazonosito from biralat as B where B.biraloazonosito=U.azonosito limit 1)
Ez szinte azonosan az előzőhöz 2perc 10 másodperc alatt futott le.

Kipróbáltam egy harmadi módszerrel is:

select U.azonosito, B.msazonosito from user as U left join biralat as B on U.azonosito=B.biraloazonosito where B.cikkazonosito is null
Ez szintén 2 perc 7 másodperc alatt futott le, és azt írta, hogy: Query took 46.6078 seconds.

Szóval a kérdésem, hogy normális, hogy egy ilyen lekérdezés ilyen rekordszámok mellett ilyen sok idő alatt fut le?
Ha nem normális, hogy lehetne gyorsítani rajta?
Mutasd a teljes hozzászólást!
Miért varchar az azonosító?
explain-al nézd meg használ-e indexet.
INT jobb lenne kapcsolóként... IMHO
Mutasd a teljes hozzászólást!

  • Van egy külön program, amivel Php nélkül lehet tesztelni, az a neve, hogy mysql(1)
    Mutasd a teljes hozzászólást!
  • Amikor ezt a nemzetközi internetes folyóirat honlapot még 2006-ban elkezdtem programozni, akkor úgy gondoltam, hogy mivel a felhasználók egy űrlap beküldésekor láthatják a post-ban az azonosítójukat, így ha az ez sima egyesével növekvő integer lenne a user-ek azonosítója, akkor esetleg rosszindulatú vagy unatkozó hackerek könnyebben korrumpálhatják az adatbázist. Ezért gondoltam, hogy a usereknek egy ilyen 20 karakter hosszú egyedi azonosítójuk legyen.
    Mutasd a teljes hozzászólást!
  • Oké, és annak van valami nem konzolos hanem grafikus felhasználói felülete?
    Mutasd a teljes hozzászólást!
  • Ha PuTTY annak számít... Miért kellene egy sebességteszt kattintgatós legyen?
    Mutasd a teljes hozzászólást!
  • Nézd meg, hogy ha mindezt a mysql-t futtató szerveren, lokális mysql kliensből futtatod, akkor mi történik, mennyi időnek érzed úgy!
    Csak tipp, konkrétabb infók nélkül másra nem futja, de ha igazam van, ott tényleg annyi időt vesz igénybe, amennyit a query futásidejére ír. A maradék időben adatokat küldözget a mysql szerver és a kliens (jelen esetben ez a kliens lehet akár a web szervered is) között. 
    Ha így lenne, megoldást nem tudok rá, de akkor nyugodt lehetsz, hogy az adatbázis szerver rendben van.
    Ilyet pár hete játszottam, csak én python-postgresql párossal.
    Mutasd a teljes hozzászólást!
  • Egy helyi teszt verzióban futtattam a lekérdezéseket a saját gépemre telepített php mysql apache (xampp) rendszerben. Azaz mind a webserver, mind a mysql server ugyanaz a gép volt, és a hálózatot sem kellett igénybe vennie az adatok küldözgetéséhez.
    Mutasd a teljes hozzászólást!
  • Hát akkor passz.
    Esetleg memória problémára tudok még tippelni. Úgy értem, hogy talán kevés ami szabadon van.
    Mert egyébként azokat a másodperc alatti időket nem tudom hova tenni.
    Mutasd a teljes hozzászólást!
  • Én a következőket próbálnám ki, hogy mennyi idő alatt futnak le:

    Összes user lekérdezése:
    select azonosito from user;
    Aztán a következő, amiben azok vannak akik bíráltak, összekötve a biralat táblával:

    select azonosito from user u, biralat b where u.azonosito=b.biralatazonosito;
    Majd egy ilyet, amikor az összes user-ből kivonjuk azokat a usereket akik bíráltak, így elvileg azok maradnak akik nem bíráltak :

    select azonosito from user minus select azonosito from user u, biralat b where u.azonosito=b.biralatazonosito;
    Egyébként érdemes belemélyedni a végrehajtási terv tanulmányozásába is, hibakereséshez teljesítmény optimalizáláshoz egy remek eszköz:

    MySQL :: MySQL 5.7 Reference Manual :: 13.8.2 EXPLAIN Statement
    Mutasd a teljes hozzászólást!
  • Nincs minus művelet mysql-ben, ezt az általam leírt módokon lehet imitálni.
    Viszont megcsináltam, hogy a user adattáblába az azonosito varchar(20) oszlop elé csináltam egy id_user oszlopot, ami int típusú, valamint a biralat adattáblába is a biraloazonosito varchar(20) oszlop elé egy id_biraloazonosito int oszlopot, ezeket értelemszerűen feltöltöttem a megfelelő értékekkel, és ezekre az oszlopokra lefuttatva a lekérdezéseimet azok egy pillanat alatt lefutnak.

    Úgyhogy ezek szerint ilyen nagy különbség van, hogy varchar(20) vagy int szerint kell keresnie az sql-nek az adatokat.

    Úgyhogy most elkezdtem az összes adattáblába felvinni az int típusú oszlopokat.

    Mivel pch javasolta ezt, az ő hozzászólását fogadom el, de nagyon szépen köszönöm mindenkinek a tanácsait.
    Mutasd a teljes hozzászólást!
  • A varchar-ra nem tud olyan index táblát csinálni mint az int-re. Főleg ha hasonlítod akkor a varchar minden elemét hasonlítja a másik tábla összes varchar eleméhez és nem használ indexet. Ezt az explain-nal is tudod ellenőrizni.
    Mutasd a teljes hozzászólást!
  • Csak egy tipp, nem tudtam, hogy MySQL-ben van ilyen: hash index. Ez felgyorsítja a varchar mezők indexkezelését, ha jól értem, viszont kizárólag arra jó, hogy egyenlőséget vizsgálj velük.
    Viszont azt hiszem, neked ez is jó lett volna.
    (bár én eddig nem vettem észre, hogy drasztikus lassulást okozott volna egy varchar mező alapján felépített index - igaz, a MySQL-t kerülöm, amennyire lehet)
    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