Mysql SELECT ... IN (SELECT ...)

Adatbázis: SQL, XML DB

Sziasztok lenne egy érdekes kérdésem. Egy lekérdezésre felírok 3 változatot és hogy mennyi idő alatt futnak le. A kérdés az lenne hogy mért, illetve milyen mysql beállításom van elszúrva...

1.
$sql="SELECT id, nev FROM kategoriak WHERE id IN (SELECT kate_id FROM cegek_kategoriai WHERE ceg_id=".$id.")";

1.4 sec
---------------------------------------------------------------
2. $sql="SELECT id, nev FROM kategoriak as k, cegek_kategoriai as ck WHERE ck.ceg_id=".$id." AND k.id=ck.kate_id";

1.1 sec
---------------------------------------------------------------
3. (és ez az érdekes)

$sql="SELECT kate_id FROM cegek_kategoriai WHERE ceg_id=".$id;
$tmp=select($sql);

$st="";
for($k=0;$k<count($tmp)-1;$k++){
$st.=$tmp[$k]['kate_id'].",";
}
$st.= "0";
$sql="SELECT id, nev FROM kategoriak WHERE id IN (".$st.")";

0.045 sec! (45ms)
----------------------------------------------------------------

Mért lehet gyorsabb a 3-as mint az 1-es ,más ötletem nincs csak hogy minden kategóriára lefut a belső select, ha ez így van, lehet ezt orvosolni?

  • 5816 megtekintés

( andrej_ v | 2010. 07. 16., p – 13:35 )

A 3-as memóriából megy teljesen és néhány darabos (50-100) elemű tömbig nem is lesz gondod, csak memóriát zabálsz, valamint az elsődleges kulcs alapján megy a query ha jól sejtem.

A 2-es viszont futtasd le EXPLAIN-el és mutasd meg az eredményét, majd utána vedd ki az "as k", és az "as ck" -t és annak megfelelően hivatkozz a táblákra is. Erről is nézz egy explain-t.

Az 1-esnél szintén az EXPLAIN-t érdemes nézni.

2-est elvileg fel tudod gyorsatani ha csinálsz egy ceg_id -t ÉS a kate_id -t tartalmazó indexet.

Na megvan én voltam figyelmetlen EXPLAIN -al észrevettem, hogy nem voltam körültekintő és az egyik mezőn nem volt index...
Köszi a tippet, látszik az egész hetes túlóra egyre szórakozottabb vagyok :)

Ettől még érdekes, az egyes és 3as közti különbség. Nem értem a logikát, hogy mért nem a belső SELECT fut le hamarabb.

Amennyire én tudom

SELECT kategoriak.id, kategoriak.nev FROM kategoriak , cegek_kategoriai WHERE cegek_kategoriai.ceg_id=".$id." AND kategoriak .id=cegek_kategoriai.kate_id

és a

SELECT kategoriak.id, kategoriak.nev FROM cegek_kategoriai JOIN kategoriak ON kategoriak.id=cegek_kategoriai.kate_id WHERE cegek_kategoriai.ceg_id=".$id

között nincs különbség ugyanannyi idő alatt fog lefutni, mivel ugyanaz is hajtódik végre.
Mindkettő JOIN

azért egyáltalánnemjó, mert Descartes szorzatot hoz letre, majd abban szur.

az elsővel az a baj, hogy minden sorra lefut a subquery és annak eredményében a keresés (szajnossz).

a 3. azért gyors, mert először egy táblából kilopsz pár sort, majd azt fixen átadod, nincs subquery, nincs join.

--
"SzAM-7 -es, tudjátok amivel a Mirage-okat szokták lelőni" - Robi.

( uid_194 | 2010. 07. 16., p – 13:39 )

Tippre a kategoriak tabla minden sorara lefut a belso select, es azert lassu, igen.

Megoldas lehet ha joinolsz esetleg, akkor egyszer osszerakja, aztan keresgel.

( cheeky v | 2010. 07. 16., p – 13:42 )

És persze megfogalmazhatnád az egészet IN helyett EXISTS-szel is...

( flatline | 2010. 07. 16., p – 14:33 )

Az elso egy klasszikus"NEVER do it" :)  
IN (SELECT ... ) tabu

Ha a masodik lassu az azt jelenti hogy a tablaknak nincs megfelelo indexuk
indexelned kell a cegek_kategoriai.kate_id es/vagy cegek_kategoriai.ceg_id es a kategoriak.id
Azonban ha ezt nem tetted volna meg valahol, akkor a harmadik is lassu lenne...
Az elso kettore: http://dev.mysql.com/doc/refman/5.1/en/rewriting-subqueries.html

A harmadik teljesen jo ( azaz a jo megoldas ), az elso kettovel ellentetben.

( Emulgeator | 2010. 07. 16., p – 14:19 )

Erdemben ugyan nem foglalkozom a problemaddal, mivel egy explain es egy kis doksi olvasas mindent megold, de egy jotanaccsal megis ellatlak:
Query teszteleskor mindig tegy ki legalabb ennyir a query elejere: SQL_NO_CACHE
Es akkor relevansak is lesznek a kapott futasi idoid.

------------------
http://www.youtube.com/watch?v=Sf8cM7f6P2I

Osszetettebb tombok eseten, foleg, ha teljesitmenyigenyes es ha lehet (0-tol indexelt tomb), akkor gyorsabb ez:

[code]
$nCount = count($aArray);

for ($i = 0; $i < $nCount; $i++) {
$aRow = &$aArray[$i];

// ...
}

(Nem olyan hosszu, celszeru ra csinalni egy templatet).

Amikor csak egy primitiv erteke van a tombnek, akkor viszont a foreach gyorsabb.

----------------
Lvl86 Troll

( Foszto | 2010. 07. 16., p – 15:09 )

Teszteltem újra módosítások után. Még mindig a 3-as a leggyorsabb, de nincs jelentős különbség a 3-as és a 2-es között már. (Ha tovább nő a tábla elvileg lesz)
Így marad a 3-as, végűlis a véletlenszerű sorok lekérdezését is így oldom meg ORDER BY RANDOM() helyett két sql lekérdezéssel és egy forral. Az is sokkal gyorsabb de az logikus is volt.
Itt az 1-es lassúsága abszolút nem logikus, érthetetlen számomra mért nem a belső SELECTet futtatja hamarabb a MYSQL.

Minden építő jellegű kommentet köszönök. Azt hiszem lehet zárni a topikot.

( xclusiv v | 2010. 07. 16., p – 16:47 )

Na belebüfögök kicsit, hátha hasznos lesz valakinek. Tehát:

Tapasztalatom szerint a mysql nem szereti a subselect-et, inkább a join a jó, míg pl. a firebird imádja a subselect-et és a join-t nem kedveli annyira.

Illetve join-nál, ha nagyok a táblák, és kis resultset kell belőle, akkor célszerű a nagy táblákat join előtt szűrni, tehát a tankönyvi példa

select *
from forgalom as f
left join termek as t on t.termek_id=f.termek_id
where f.datum='2010-07-04'

helyett inkább így csináld:

select *
from (
select *
from forgalom
where datum='2010-07-04'
) as as f
left join termek as t on t.termek_id=f.termek_id

, ugyanis így 3 éves forgalmat nézve kevesebb, mint ezred részére csökkentetted a join egyik tábláját, ezzel a motor munkáját is.

Valahogy így átírva a topicindító 2.-es példáját (straight join helyett left és szűrni) szerintem gyorsulna.

A példák nem biztos, hogy működnek, csak példák.

( uid_17626 | 2010. 07. 16., p – 19:13 )

Tömd meg jól adattal a táblát és nézd meg utána.

( Foszto | 2010. 07. 17., szo – 10:17 )

@Oregon - Nagyobb adatbázison teszteltem mint amin élesben fog futni.

Nem túl nagyok a táblák van kb. egy 300 000 ezres cég tábla egy 50 000-es kategória és egy 450 000 összekötő tábla a legnagyobbak. Ennél sokkal nagyobb táblákkal (milliárdos nagyságrend) is dolgozta de itt több bonyolult lekérdezés is nehezített (random megjelenítés kulcsszavakra ami a relevancia megőrzése érdekében súlyozva is volt, emellett vannak kulcsszavakra fix helyezések is. De a fenti példa, mivel akár 20-szor is le kellett fusson, jelentősen befolyásolta a sebességet).
A dolgom az volt, hogy a kezdeti 10-18 seces megjelenítési időt 1 másodperc alá gyúrjam egy oldalon. Most a megjelenítés ~200ms mindössze (ez természetesen böngészőfüggő is) ami ~80ms ping idő mellett már bőven megfelelő. Persze ehhez nem csak a lekérdezések optimizálása járult hozzá de jelentős sebességjavulást okozott, na de ez már off.

@xclusiv a példádat kipróbáltam, jelenleg nem észlelek különbséget, ez valószínűleg azért van mert most már a lekérdezési idők 0,0006 és 0,0008 sec között vannak. ez még ha 20 alkalommal le is kell fusson mindössze 0,012 és 0,016 sec (mindkét megoldás e közé az intervallum közé esik, mig a 3. példa valószínűleg azért mert két sql lekérdezés van benne, így kisebb adatbázissal némileg lassabb). Nagyobb tábláknál valószínűleg előjönne a különbség, de nem fog már jelentősen növekedni az adatbázis. Ettől függetlenül amiért nem látszik biztosan spórol erőforrást.
Köszi a tippet. ha itt nem is máshol biztosan nagy hasznát veszem majd.