SET mezőtípus probléma
Sziasztok!
Van egy táblám, amibe van egy ilyen értékeket tárolni képes SET típusú mezőm:
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
Néztem, hogy van egy nagyon jó függvény: FIND_IN_SET.
A problémám az, hogy ez csak egyetlen egy értéket tud ellenőrizni.
Nekem arra lenne szüksége, hogy pl. azt kérem a szervertől, hogy határozza meg azokat a sorokat amiben a SET típusú mezőben 1 vagy 2 szerepel (nem 11, 12, stb. amiben a két számjegy szerepel).
Eddig azt találtam csak, hogy csináljak egy csomó OR-OR-OR-t egymás után.
De egy nagy táblán egy mondjuk csak 8 érték kikeresése miatt készített plusz 8 OR az nem a legszerencsésebb.
Próbáltam IN-el, de azzal nem működik.
Van valakinek valami jó ötlete?
Köszi.
■ Van egy táblám, amibe van egy ilyen értékeket tárolni képes SET típusú mezőm:
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
Néztem, hogy van egy nagyon jó függvény: FIND_IN_SET.
A problémám az, hogy ez csak egyetlen egy értéket tud ellenőrizni.
Nekem arra lenne szüksége, hogy pl. azt kérem a szervertől, hogy határozza meg azokat a sorokat amiben a SET típusú mezőben 1 vagy 2 szerepel (nem 11, 12, stb. amiben a két számjegy szerepel).
Eddig azt találtam csak, hogy csináljak egy csomó OR-OR-OR-t egymás után.
De egy nagy táblán egy mondjuk csak 8 érték kikeresése miatt készített plusz 8 OR az nem a legszerencsésebb.
Próbáltam IN-el, de azzal nem működik.
Van valakinek valami jó ötlete?
Köszi.
addig jutottal el
A kavarodás elkerülése végett vegyük úgy, hogy a SET értékei ezek lehetnek:
SET('kutya','macska','elefánt')
Ha tudom, hogy a SET első és második értékére vagyok kíváncsi (tehát a kutyákra és a macskákra), akkor az így van tárolva: 00000011, tehát decimális 3.
Ekkor lehet ezt használni:
SELECT * FROM `tábla` WHERE `mezo` & 3
vagy
SELECT * FROM `tábla` WHERE `mezo` & 3 = 3
Az első egy 'OR' a második egy 'AND' műveletet jelent ez esetben a keresett SET első és második értéke között (kutyák és macskák).
A problémám az, hogy hogyan tudom meghatározni hogy a kutyák a macskák és az elefánt miként van tárolva az adott mezőben.
Mondhatnám, hogy egy INSERT-el belepróbálom azt, hogy kutya + macska, és gyorsan kiolvasom, hogy milyen értéket vesz fel, de ez annyira rossz megoldás, hogy csak na.
Találtam olyat, hogy MAKE_SET, ami majdnem azt csinálná amit kell, csak pont fordítva teszi mindezt. Ott van egy string, és bit alapom megmondhatom, mely elemekre vagyok kíváncsi. Nekem az kéne, hogy van egy összeállított stringem, és ő mondja meg, hogy az binárisan minek felelne meg.
Kell a SET?
Nem azért mondom, de ha később pl. szükség lenne a "zsiráf" érték felvételére is, akkor mit csinálnál?
Én valószínűleg helyből egy vagy két másik táblával oldanám meg, attól függően, hogy az értékek közül egy (master)rekord egyet vagy többet vehet fel. Külső kulcs(ok), azt jónapot.
Hirtelen nem is jut eszembe olyan komolyabb példa, ahol láttam volna SET-et használni (annyi jobb megoldás van), de ez nem feltétlen jelenti azt, hogy te se használd.
8 or sok?
Másrészt ha bitműveletekkel akarsz szelektálni, akkor nem SET-et használnék a helyedben, hanem más típust, ami inkább erre való, és akkor tiszta a helyzet.
Jó kérdés
Amit a bitműveletről írsz, az nagyon jó lehet, ha nem kell belőle szöveges kimenetet (is) kreálni. Nekem nem is jutott eszembe...
válasz
Írtátok a külön táblát. Jó megoldás és használom is azon adatok esetén ahol nem kevés véges és akár folyamatosan bővülő értékek lehetnek. A SET-et azért gondoltam a kis elemszámú értékekre használni, mert ez egy több mint 80 tulajdonságot nyilván tartó hatalmas ügyféladatbázis lenne. Gondoltam egy max 10 elemet tartalmazó tulajdonságot talán így sebesség szempontjából célszerübb tárolni. Azért gondoltam ezt, mert azokat a tulajdonságokat, melyeket külön táblában tárolok, azokra egy keresés esetén sokkal bonyolultabb keresni. Írtátok a set leváltását is. Bár azt a tipust nem szoktam használni, de akár lehet az is valamire jó, mert most a set-en már nem szövegkémt, hanem számokkal tárolom a lehetséges értékeket. Így a többi résszel egységes kezelést kaptam, és a weboldalon is talán egy trükkel kezelhető a dolog. Ha beérek felvázolom a teljes szerkezetet, hátha az segít megtalálni az ideális megoldásra.
a szerkezet
Tehát:
Van egy "attributum" tábla. Ebbe van felsorolva az összes tulajdonság, és azok jellemzői (jelenleg 85 rekord van benne).
Ennek több szerepe van:
- segíti a PHP-t, hogy hogyan kell az egyes tulajdonságokat megjeleníteni (pl. szöveges bevitel, legördülő lista, stb.)
- segít a tulajdonságok leképzésében, illetve a keresi feltétel összeállításában
(ha keresünk az ügyfelek közt, akkor első körben mindig a PHP ebből a táblából kiolvassa azokat a tulajdonságokat, amiket a dinamikusan létrehozandó SQL-ban szükség lesz, és az „sql_mapping” és a „sql_mapping.search” mezőkben talál instrukciókat az SQL parancs összeállítására.)
Van egy "attributum_value" tábla. Azokban az esetekben amikor egy tulajdonság esetén az értékek nem tetszőlegesek lehetnek, akkor itt vannak tárolva a lehetséges választató értékel, hogy azok milyen sorrendben következnek, illetve a weboldalon milyen szöveggel kell megjeleníteni őket, stb.
Van egy "profile_attributum" tábla. Ebbe kerülnek azon tulajdonságok tárolása, amiből egy ügyfél több értéket is felvehet, de már nem tárolhatók SET-ben.
Vagy azért, mert nem 1-2 elemet lehet csak kiválasztani, vagy azért, mert a lehetséges elemeket a felhasználók folyamatosan bővíthetik (mint a Facebook esetén).
(természetesen partíci onálva lenne)
Természetesen van egy "user profil" tábla is, melyben az ügyfél azon tulajdonsága vannak, amik vagy egyértelműsíthetők (név, testsúly, stb.), vagy SET-ben még tárolhatók.
Azok, melyek csak a "profile_attributum" táblában tárolódnak, azok a mezők nincsenek itt felvéve. Azok számolás alapján aliasként keletkeznek.
Alap esetben ha lekérdezem a táblát, akkor látom, hogy
Anna 160 cm magas vékony és szemüveges.
Béla 170cm magas, sportos és szemüveget és lencsét is visel.
Géza 190cm magas, átlagos és jó a látása.
Ha keresni akarok a lencsét és szemüveget viselőkre, akkor tudnom kell, hogy a MYSQL a szemüveget a 2^0 a lencsét a 2^1-en tárolja.
Tehát ha mind a kettőre szeretnék keresni, akkor 2^0+2^1=3(decimális)-ra kell keresnem.
EZ VOLT AZ ALAP KÉRDÉS!
Mivel nincs jobb megoldásom még, ezért arra a trükkre gondoltam, hogy a "attributum_value" táblában az értékek nagyon szépen jelezhetnék a 2 hatványait, hogy az adott érték, mely hatványon szerepel.
Tehát mikor elkészítem a HTML oldalt, akol a check-boxok vannak, akkor az egyes checkboxok az alábbi értékeket hordoznák:
Ha nem formból érkezik az érték, hanem mondjuk az oldalt néző ügyfél adatait kéne behelyettesíteni, akkor a SET mezőt nem normálisan szövegként, hanem számként kérem kiolvasásra (az keresési SQL előállítása elött):
Mint írtam azért gondolkodok SET-ben aminél csak lehet, mert azokat az adatokat amik a "profile_attributum" táblában vannak csak úgy tudom kiolvasni (de főként keresni), hogy GROUP BY kell alkalmaznom.
Ekkor a hobbira létrejön egy "hobby" alias, melyben vesszővel elválasztva szerepelnek az ügyfélre vonatkozó értékek .
Ha keresni is kell, akkor azt az alábbi módon tudom megtenni:
ekkor persze az alias-os mezőben nem minden érték szerepelhet ami az ügyfélre jellemző, csak az ami a keresési feltételeknek is megfelel. De ez nem baj, mert igazából azt szeretnénk tudni, hogy valamelyik hobbit űzi –e az ügyfél. Ha igen, akkor az adatlap kiírásakor elég a pontos értékeket lekérdezni.
Ráadásul ha a feltétellistában több olyan tulajdonság is szerepel egyszerre amit a "profile_attributum" táblában kell tárolni, akkor egy picit kell még szórakoznom, hogy tényleg ezek is - egymással legalábbis - AND kapcsolatban legyenek.
Mielőtt az ügyfél kereséseket elkezdené a rendszer , az előtt kiolvassa azokat a rekordokat az "attributum" táblából, melyeket használni kell, és az sql_mapping és sql_mapping.search mezőkben megkapja azokat az instrukciókat hogy miként kell dinamikusan a megfelelő SQL utasítást összeállítani.
A fentiek tényében szeretném akkor a meglátásaitokat kikérni.
Fontos, hogy a lehetséges értékeket tényleg valami egységes formában lehessen tárolni (mint ahogy ez az "attributum_value" táblában most van (főleg, mert némelyik tulajdonságot az ügyfelek folyamatosan bővíthetik)).
- A SET-et miként lehetne leválna valami másik bit műveletekre optimalizáltabb mezőt használni?
- Amit a "profile_attributum" táblában tárolok, arra hogyan lehetne esetleg szebben, gyorsabban, jobban keresni, netán tárolni.
Szeretném, ha több millió ügyfelet is (ha nem egy gépen a későbbiekben), de gond nélkül gyorsan tudna kezelni.
Segítségeteket előre is köszönöm.
Zoli
Integer
Van egy olyan gyanúm, hogy az egész feladatot meg lehetne oldani lényegesen egyszerűbben is, de ez csak gyanú. Mindenesetre én újragondolnám ezt az attribútumtábla-dolgot, hátha nem kell adattáblának irányítani a PHP-t. Vagy ha mégis, akkor minél kevesebb lépésből álljon egy keresés.
Megfontolandó még a szerializált adatok használata, bár ilyenben keresni nagyon gázos, viszont tárolásra tök jó.
Ha jól értem akkor pl nem
A bolen tömböt arra írtad, hogy esetleg az INT határait tovább lehessen feszegetni, ha meg tudom határozni a lehetséges elemek maximális számát? Tehát esetleg nem kell a kapcsolótáblába tennem olyan adatokat amik bár sok elemből állnak, de előre meg tudom mondani, hogy mondjuk 300-nál tuti nem lesz több? Tehát egy hajstílust, piercing tíust tehetnék ilyenbe akkor is ha a felhasználók bővítik a listát valamelyest.
Természetesen ezt ne használjam a kedvenc film tárolására, mert abból több millió is lehet.
Az hogy a logika egy része SQL-ben van az saját döntés, mert segíti a moduláris felépítést. Mivel az csak az oldal működésének csak kis szelete.
Mindjárt lemerülök , így most elsőre gyorsan csak erre reagálok.
Nem
És ez csak a SET kiváltására / gyorsabbá tételére jó, ha külső táblával akarod, akkor kell egy merge-tábla is. Ez sokkal szabadabb felhasználást biztosít, de valószínűleg lassabbak lesznek a lekérdezések. Neked kell tudni, melyik éri meg neked jobban.
Ja bocs félre értettem, de
Utólag nem akartam módosítani.
Ha jól értem ha BIGINT használok az 8 byte-on van tárolva, tehát 64 értéket tudok tárolni. Ez pont megegyezik a SET által tárolni képes értékkel.
Tehát egy tinyint-tel azt érem el, hogy még kisebb értéken tudom azt a 6-10 variációt tárolni mint sima SET esetén. Cserében nem kérhetem ki vesszővel elválasztva az értékeket mint SET esetén, és a FIND_IN_SET -ről kell végleg elfelejteni.
A MARGE táblát hogy érted, és a serializálást?
Valahogy pl a BLOB-ot nem
Mert akkor pl ez a maga 65535 byte-ábal 8*65535=524280 különböző adatot tudna tárolni (és ettől is van nagyobb).
Persze a kérdés, hogy tuna -e ekkora adattal valahogy számolni, azokon bit műveleteket végezni.
Mert akkor még ez is szóba jöhetne. Bár sejtem, hogy ez már nagyon valóságtól elrugaszkodott adat, hogy egy mezőben pl. egy filmest adatbázis lehetséges értékeit is tároljuk...
Inkább csak agygyakorlatnak írtam ezt is...
A feladatnál az a nehézség kicsit, hogy nálam mindenre rá kell tudni keresni, vagy ügyfélszám esetén, és mindezt gyors futás mellett.
Nem értem, miért nem csinálsz
Mint láthattad van ilyen rész
De mivel mindenre kell tudni keresni, (és több mint 85 tulajdonság van) ezért én csak a fent jelzett viszonylag összetett megoldást tudom rá (mert egy ügyfél egy-egy tulajdonságból több,vagy vígtelen értéket vehet fel egyszerre) Ninvs szinte olyan adat amire nem lehetne keresni,és az esetek többségében szerintem sokszor legalább 10-25 tulajdonságra kell tudni szűrni párhuzamosan. Sőt mikor az ügyfél álítja a feltételeket, már akkor meg kell tudnom mondani, hogy hánny találatot fog kapni. Tehát a konkrét keresés elött, már gyors eredményt kell produkálnom.
Most is 23 tulajdonság van ilyen külső táblában, és további 19 tulajdonság van ilyen SET-ben tárolva. (szerencsére a legtöbb, a keresésben részt vevő adatot sikerült ebbe a 19-be elhelyezni).
Úgy érzem ha 43 mezőre csak ezzel a GROUP BY megoldással tudnék keresni, akkor mind az ALIAS-ok, mind a GROUP_CONCAT-ok, és a HAVING-ek száma is elérhetné egyesével kereséskor a 43-mat.
Ez egy több milliós ügyfeles közösségi oldalnál szerintem már hatalmas terhelést jelenthetne.
De természetesen bízok benne, hogy tudtok mondani (javasolni,közösen. kitalálni) valami jobb módszert pl. a külső táblákban tárol adatok szimultán keresésére.
Vagy valami más módszert ami biztosan reális futásidőt biztosít majd.
Ne szerializálj, ha
"MERGE" :
- A
userstábládban van ugye egy user_id.- Csinálsz egy
propertytáblát a neked kellő mezőkkel és prop_id-vel. Ebben egy rekord egy tulajdonság.- Csinálsz egy harmadik táblát (
users_prop), amiben összesen két oszlop van: user_id és prop_id. Ez a tábla "rendeli hozzá" a userekhez a tetszőleges számú tulajdonságokat.- Ilyen esetben kb. kötelező külső kulcsokkal összekapcsolni a táblákat, ennek keress utána.
Ezt ha meg tudod lépni úgy, hogy összesen a 3 tábla meghatároz minden Júzer-dolgot, akkor nyert ügyed van. Mondjuk milliós ügyfélnél már kellhet a queryken variálni, de azt ráérsz akkor, ha eléred.
Szerk.: írhatnál kicsit szebben-helyesen.
Pepita! Köszönöm a
Köszönöm a választ.
(elnézést, de sokszor telefonról írok, ezért a sok elütés)
Amit írtál, az volt nálunk is a kiinduló állapot.
Viszont pont a gyors és összetett kereshetőség volt a gondunk vele.
Pl. olyan ügyfeleket keresek, akik
- (kék OR zöld OR barna szemű) AND (középiskola OR FŐISKOLA) AND .... tulajdonságokkal rendelkező egyéb ügyfeleket keresnek.
Tehát a te users táblád = az én user profil táblámmal, a property = profile_attributum és a users_prop = attributum_value.
A attributum_value táblában csak azért van 3 mező 2 helyett:
- könnyebb legyen a csoportosítást elvégezni
- illetve a SET végett szerettük volna ha tulajdonságonként előröl indulhatna a felvehető értékek száma (egységes kezelés miatt).
De mint írtam ez az egész SET (vagyis most tyniint már) onnan jött, hogy elég bonyolultan tudjuk csak rákeresni összetett OR AND összefüggésekre.
Említetted, hogy query-n lehet optimalizálni, hogy szebb legyen.
Igazából ez érdekelne, mert akkor lehet elhagyni ezeket a BIT alapú vizsgálatokat (legalábbis ahol kevés és véges számú elem van), ha cserébe nem az lesz, hogy minden egyes query 1-2ezer karakter hosszú (ami még nem is annyira gáz, hanem hogy egy csomó GROUP_CONCAT-ot, HAVING-et és feltételt tartalmaz).
(Sajnos az gond lenne, ha már akkor kezdenénk átalakítani alapjaiból a teljes adatszerkezetet, amikor már élesben megy az oldal, több milliós adatbázissal...)
Segítségeteket előre is köszönöm.
Ez a többször említett
Még nincs meg a milliós
Azért kell készülni rá, mert egy TV reklám kampánnyal indulna majd a szolgáltatás.
Ezért arra nem lesz lehetőség, hogy 1 hónap után alapjaiból írjuk át az adatszerkezetet majd.
Partícionálni szeretnénk, méghozzá pont amiatt, hogy tulajdonságok külön partíción legyen tárolva a kapcsolótáblában. Így nem használja a tábla szemszínei tároló részeit, ha arra éppen nincs szükség.
Ez a HAVING-et mi is szeretnénk eltüntetni (ha csak EGY ilyen több elemes kapcsolótáblás dologra kell szűrni, akkor nincs is rá szükség), de a különböző tulajdonságok (amik egyszerre több értéket is felvehetnek) közti AND kapcsolat kifejezésére nem találtunk egyenlőre más megoldást.
Mert ha az elvárt szemszín: kék, zöld vagy barna lehet, de fontos, hogy az elvárt iskolai végzetség szakközépiskola, vagy egyetem legyen, akkor HAVING nélkül megkapjuk a kék, zöld, barna szeműeket elváró ügyfeleket, de azok egy részének lehet hogy a 8 általános végzettség is elég.
de ha azt nézzük a SET (vagy kiváltója) is pont a lehetséges HAVING-ok esélyét hivatott csökkenteni, mert csak AND-ekkel illesztett bit szintű műveletekkel váltja ki az egészet
Tehát mi is ezt a kapcsolótáblás módszert preferálnánk, viszont mintha kereséskor ilyen bonyolult kifejezésekkel lehetne csak szűrni bennük.
És tényleg köszönöm mindenkinek, aki próbál segíteni, mert bízom benne, hogy sikerül valami jó megoldást találni a problémára közösen, így vagy úgy.
Az idézett lekérdezésben
Egyébként izgalmasan hangzik a dolog, drukkolok, hogy összejöjjön, és ha publikus lesz, hogyan sikerült megoldani, annak örülnék, mert kíváncsi vagyok.
Sajnos azért nem a WHERE-ban
Tehát ha lekérdezem, hogy mely ügyfelek kérnek kék(1), zöld(2) vagy barna(3) szemű ügyfeleket, akkor első körben kapok egy csomó rekordot.
Ebben minden ügyfél többször is szerepelhet a LEFT JOIN miatt, mert egy ügyfél nem csak egyféle szemszínű embereket kereshet.
Ha USER_ID szerint GROUP_BY-olom az ügyfeleket, akkor ALIAS-okba csoportosítva (a GROUP_CONCAT segítségével) meg fogom kapni (a szűrt tulajdonságok értékek közül), hogy egy-egy ügyfél esetén mely tulajdonság-feltételnek felelt meg az ügyfélrekordok.
Tehát ha az ügyfél keresett kék(1) vagy barna(3) szeműeket, akkor a
Ez így helyes is. Az elvárt szemszínek között valóban OR kapcsolat van, mert nem kell mindegyikkel rendelkeznie az ügyfél elvárásainak.
De ha azt is szeretném figyelni AND kapcsolattal, hogy a keresett partner iskolai végzettségei között szerepeljen a főiskola(1) vagy az egyetem(2), akkor a fenti példa az alábbi módon néz ki.
Ekkor a 'keresett_szemszin' ALIAS-ban szerepel a kék szemszín értéke(1), de a `keresett_vegzettseg` ALIAS-ban NULL fog szerepelni.
Az, hogy az egyes ALIAS-okba mindbe van -e legalább egy érték azok közül amiket keresünk, azt ebben a módszerben csak akkor derül ki, amikor GROUP BY és a GROUP_CONCAT hatására megszületik az ALIAS-ok értéke. Ha bármelye ALIAS NULL értéket ad vissza, az azt jelenti, hogy a szűrési feltételnek csak részben fele meg az ügyfél (pl. a szemszín elvárásai megfelelnek, de a végzettségbeli elvárásai viszont nem).
Az általunk kitalált módszer a WHERE-ben csak OR kapcsolatot tud figyelembe venni, mivel a LEFT JOIN által talált rekordok közt AND kapcsolatot nem tudunk figyelni.
Ha a WHERE feltételbe AND kerülne, az nem adna vissza értéket, mert az olyan feltételt alkotna, melyek kizárják egymást. Azért, mert a kapcsolótáblában a tulajdonságkapcsolatok rekordonként helyezkednek el. Így soha nem lesz olyan rekord, ami egyszerre tárolná a kék szemszínt és a főiskolai végzettséget.
Természetesen, biztos lehet más megközelítési módszer is a problémára, ami lehet sokkal jobb.
Mi megfelelő indexekkel, és a minél több adat SET (tinyint)-be való pakolásával (mert ezek is a WHERE-ben egy AND BIT művelettel elintézhetők) próbáljuk most a tervezéskor elérni, hogy lehetőleg minél kevesebb rekord jöhessen már szóba (minél több (ügyfél) találatot zárjunk más miatt már ki előre), mire a kiértékelés az ilyen kapcsolótáblabeli tulajdonságokhoz ér.
Így ha nincs jobb módszer esetleg, akkor abban bízunk, hogy eleve mondjuk a több milliós ügyfélkörből hátha már csak pár százat kell GROUP BY-olni a kapcsolótáblával, mert a WHERE feltétel miatt hátha a többség már kizárta magát.
De ha van esetleg jobb, vagy más szemlélet, akkor lehet nem csak bízni lehet ebben.
Részben poén, de nem
Nem értem miért van ez a
Nem az OR-ral van a baj.
Ha OR-ral kellene összeköti a különböző eltérő tulajdonságokat, akkor nem is lenne baj.
Ha az a feladat, hogy keressek olyat aki kék, zöld vagy barna szemű embert keres, vagy főiskolást, vagy egyetemistát, akkor nem is lenne HAVING.
De ha a példánál maradunk, akkor a különböző szemszínek OR kapcsolatban vannak, és a különböző iskolai végzettségek is OR kapcsolatban vannak.
A szemszínek és az iskolai végzetségek között viszont AND KAPCSOLAT van.
Azért jelzem, hogy lehet félreértés amiatt, hogy nem olyan tulajdonságok vannak a példában amikből az ügyfél egyszerre csak egy értéket vehet fel. Tehát ha arra vonatkozott a kérdés, hogy maga az ügyfél SAJÁT szemszínt és SAJÁT végzettséget miért nem tudom egyszerűen kezelni, akkor azt jelezném, hogy azokkal nincs gond. Azok közt AND és OR relációt nem is okoz felvenni a kapcsolatot.
A kapcsolótáblás tulajdonságok azok ilyenek:
beszélt nyelvek, konkrétan miket sportol, konkrétan milyen háziállatai vannak, kedvenc ételek, könyvek, filmek, sportok, zenék, italok, játékok, stb., elvárt vallások, elvárt beszélt nyelvek, stb.
(Hogy miért kell mindenre tudni rákeresni? -> Azért mert ez a feladat!)
Belső SELECT
Én is túlbonyolítottnak érzem egyébként, de az is lehet, hogy nem szántam elég időt az írásaid átolvasására.
A belső select-et ismerem, de
Kérlek fejtsd ki, mert sokat segítene, hogy ezzel hogyan tudom az AND kapcsolatot felállítani a kapcsolótábla elemei között?
Amúgy mi is bonyolultnak tartjuk, azért próbálok segítséget kérni.
Közben arra rájöttem, hogy a felhozott utolsó példáim pont nem jók, mert a szemszín az pont hogy véges lehet (tehát pont jó a BIT művelet rá), és az iskolai végzettség is csak a több nyelvűség miatt (más országokban más végzettségek vannak) végtelen számú.
Tehát ha valaki észrevette a hibát, azt kérem nem írja, hanem vonatkoztasson el tőle.
Tehát vegyük példaként az űzött sortot és a kedvenc filmet, melyeket azt ügyfelek maguk bővítik a listaelemeket.
Segítségedet neked is és a többieknek is köszönöm továbbra is.
Továbbra sem értem a
A HAVING amúgy egy teljesen ugyanolyan záradék, mint a WHERE, csak a már elkészült eredménylistát szűri újra (nyilván menet közben aggregátumra nem lehet szűrni, hiszen az értéke még nem ismert, ezért kell HAVING-ben szerepelnie)
Akkor még mindig elbeszélünk egymás mellett
Sajnos akkor azt írod le:
Azokat a rekordokat keresed, amik a kapcsolótáblában a tulajdonság= 'hobby' és az érték 0, 1 vagy 2.
Ezen kívül legyen igaz az is, hogy a kapcsolótáblában a tulajdonság='film' és az az érték 11, 412 vagy 452.
Vajon lehet egy a 3 mezős táblában olyan érték, mely az AND miatt a 'Attributum_id' mezőjében egyszerre lehet hobby és film is.
És lehet -e olyan érték ('Attributum_value_id') ami egyben (0 OR 1 OR 2) AND (11 OR 412 OR 452). Nyílván ez sem lehet.
Az eddig általatok is mondott kapcsolótáblában, hogy mely tulajdonságok, milyen értékeket vehetnek fel, azok egymás alatt külön-külön rekordonként helyezkednek el.
User_id, Attributum_id, Attributum_value_id
1, hobby,0 (favágás)
1, hobby, 1 (futás)
1, film, 11 (Avatar)
2, hobby, 2 (játék)
2, film, 412 (Star Wars)
2, film, 452 (Hobbit)
3, hobby, 0 (favágás)
3, film, 412 (Star Wars)
4, film, 412 (Star Wars)
Ha pedig így van, akkor ezért az AND-ot nem tudom rájuk értelmezni.
De ha valamit rosszul gondolok, vagy te másra gondoltál, akkor kérlek fejtsd ki.
De segítségként.
A 'user profil' és a 'profile_attributum' LEFT JOIN-ja ilyen értékeket ad GROUP BY nélkül: ha hobby-ban IN(0,1,2) AND filmben (11,412,452) -et keressük:
User_id, profile_name, physique, ...., Attributum_id, Attributum_value_id
1, Anna, 0 (vékony), ..., hobby, 0 (favágás)
1, Anna, 0 (vékony), ..., hobby, 1 (futás)
1, Anna, 0 (vékony), ..., film, 11 (Avatar)
2, Béla, 1 (sportos), ..., hobby, 2 (játék)
2, Béla, 1 (sportos), ..., film, 412 (Star Wars)
2, Béla, 1 (sportos), ..., film, 452 (Hobbit)
3, Géza, 2 (átlagos), ..., hobby, 0 (favágás)
3, Géza, 2 (átlagos), ..., film, 412 (Star Wars)
4, Dezső, 6 (kövér), ..., film, 412 (Star Wars)
Group By User_id után megkapjuk, hogy Anna, Béla, Géza és Dezső lehet az aki szóba jöhet.
1, Anna, 0 (vékony), ..., hobby, 0 (favágás)
2, Béla, 1 (sportos), ..., hobby, 2 (játék)
3, Géza, 2 (átlagos), ..., hobby, 0 (favágás)
4, Dezső, 6 (kövér), ..., film, 412 (Star Wars)
Viszont ha itt megállunk, akkor látjuk, hogy rossz eredményt kapunk.
Mert bár Anna, Béla és Géza mindennek megfelel, de Dezső csak a film terén teljesíti az AND kritériumot.
Tehát Dezső egyik hobbit sem űzi, melyekre kíváncsiak voltunk a lehetséges filmek mellett AND kapcsolattal.
Ennek kiszűrésére vezettük be az ALIAS-okat. A group_concat hatására ezekbe az ALIAS-okba vagy valami lesz (vesszővel felsorolódnak az értékek), vagy mint Dezső esetén a hobbi ALIAS-ba NULL kerül.
Tehát:
User_id, profile_name, physique, ...., hobby, film
1, Anna, 0 (vékony), ..., hobby, '0,1' (favágás,futás),'11'(Avatar)
2, Béla, 1 (sportos), ..., hobby, '2' (játék),'412,452'(Star Wars,Hobbit)
3, Géza, 2 (átlagos), ..., hobby, '0' (favágás),'412'(Star Wars)
4, Dezső, 6 (kövér), ..., film, NULL, '412'(Star Wars)
Ha viszont ez rendelkezésre áll, akkor egyenlően a HAVING-gel azt ellenőrizzünk, hogy csak melyek azok a rekordok, amiben egyik ALIAS sem vett fel NULL értékeket, hisz ezek azok amik nem felelnek az eredeti kritérium összes feltételének.
select * from profiles pjoin
distinct-tel ki lehet szűrni az ismétlődéseket (és nyilván csak ap.névkell az eredményhalmaz oszlopaiból).Most nyilván értelmetlen a physique bevonása, mert nem szűrünk rá.
Na most már értem. A
A problémát azzal hidalod át, hogy az egyes tulajdonságok kapcsolótábláit külön táblába bontod. Ezek után az ügyfelek táblát az összes vizsgált tulajdonság-kapcsolótáblával egy nagy közös szorzatot állítasz elő. Ebbe benne lesz minden, mindennel való szorzata. Ha az ügyfél minden kritériumnak megfelel,akkor az ügyfél megfelelő variációja is.
Tetszik a megoldás!
Mivel mindketten másból indulunk ki, ezért a teljes kifejtés sokat segít a másik megoldásának megértéséhez.
Viszont lenne pár kérdésem, hátha neked, vagy másnak van tapasztalata.
Mi azért vontuk össze a kapcslótáblát (azon kívül,hogy így dinamikusabban jöhet bármikor új tulajdonság), hogy csökkentsük a JOIN-ok számát. Tehát mondjuk 5 vizsgált tulajdonság esetén ne alapban 6 táblát kelljen összeJOINolni. (Főleg mert az itt nem tárgyalt kiegészítő adatok miatt így is jöhet rá 2-3 plusz 1:1 kapcsolatú tábla.) Az hogy sok táblás teljes szorzatot képzünk, az a futásidőre mennyire van rossz hatással nagy (több milliós) rekordszám esetén?
A másik, hogy ha jól értem, ezt a megoldást is csak azokra a tulajdonságokra ajánlod, melyeket a fent írtak miatt most is kapcsolótáblákban tárolunk. Különben 15-20 táblás JOIN szorzatokkal kellene dolgozni.
Minél egyszerűbb valami,
Őszintén remélem, hogy tényleg milliós adatbázissal kell majd dolgoznotok, de nézzük a tényeket: egyelőre nincs ilyenről szó. Lehet, hogy TV-ben fogtok hirdetni, de ez egyrészt nem jelent semmit, másrészt a témában elég sok konkurens oldal van. Tehát vagy bejön a dolog vagy nem.
A magam részéről én ezt úgy oldanám meg, hogy megírnám úgy, ahogy tudom, működjön, és ha beindul a tömeg, első körben vasat tennék alá, azzal egy ideig ki lehet húzni. Akár azt is megcsinálhatjátok, hogy "felhőből" béreltek processzoridőt és memóriát, ott csak fizetni kell, és többet kaptok.
Szóval, amíg vasból oldjátok meg a dolgot, tudtok elemezni, hogy hol vannak az üvegnyakak, mit kell optimalizálni. Az is lehet, hogy tíz JOIN mellett is ásítozni fognak az adatbázis-szervereitek, de ez mindenképp csak élesben fog kiderülni.
Sőt, akár a főnökeitek megpróbálhatnának a konkurenciától átcsábítani egy tapasztalt programozót, és akkor még nagyon gondolkodni sem kell.
Úgy érzem, ha a jelenlegi úton haladtok tovább, a saját dolgotokat nehezítitek meg, és esetleg nem készültök el időre.
Köszi. Sajnos ez azért egy
Sajnos ez azért egy évtizedes vállalatnál nem így van.
Ha lassulás miatt a kampány nem éri el a célját (hiába jönnek az ügyfelek, mert el is mennek: hisz van elég konkurencia, van kihez menni), akkor egyszerűen kirúgják a csapatot, és kész.
Amúgy a kollega gondolata 4 kérdést vetett hittelen fel bennem:
- bár a megoldását értettem, de annyi kiegészítéssel, hogy ekkor a kapcsolótáblák már csak 2 mezősek. Az első az ügyfél-azonosító, a másik pedig a hobby táblában a hobby, a film táblában a film, stb. Így egyszerűbb lesz a kollega SLQ QWERY-je is, és ez biztosítja hogy még a GROUP BY előtt fizikailag is megvannak azok a mezők, amit én csak ALIAS-sal tudtam előállítani.
- lehet a JOIN helyett az INNER JOIN is működne, mert ha a logikát nézem, akkor lehet csak a valós kapcsolatokat építi fel
- létezik VIEW tábla, ahol a teljes mátrixot fel lehetne építeni. Bár úgy tudom, hogy sokan dicsérik a VIEW táblát, de úgy tudom, hogy ebben viszont nem érdemes nagyon keresni, mert akkor viszont lassabb.
- egy másik tapicban viszont volt szó a VIEW-ben való keresésről. Ott említették a materialised view táblát, ami előre fizikailag legyártott táblát jelent. Ez viszont nincs a mysql-ban. Viszont létezik valami flexviews megoldás, mely triggerekkel ezt emulálni tudja mysql környezetben is. Bár lehet hatalmas tábla lenne, ha ez egy 15-18- JOIN-t lefedő táblát testesít meg ez, de lehet cserébe marha könnyű, és gyors keresést tud megvalósítani.
Most szerintem elkezdek azon dolgozni, hogy létrehozzak valami random adatokkal valami tesztkörnyezetet, melyben mondjuk 2millió ügyfél kb. 8 millió kapcsolattábla adattával tudom vizsgálni futásidőre.
Na, azt hiszem, megvan a
Ezt most nem értem pontosan.
Ha a hatos hozzászólásban
"Gogle egyik fő
Melyikre gondolsz?
Most nagyon sötétben tapogatózok.
Nem! Én egy táblában
Ááá igazad van. Reggel még
Reggel még elég álmos voltam a vonaton, és nem vettem észre, hogy ugyanaz a tábla van mindenhol.
Még akkor sem, holott láttam, hogy ott van az atributum mező ellenőrzés is, de inkább azt hittem hogy ez hiba mint hogy rájöjjek.
Elnézést. Ezt most tényleg benéztem. Lehet a vonat késés miatt eleve túl nyűgös voltam hozzá.
És tényleg lehet valami, hogy a sok JOIN lassítana, de azzal, hogy mindig ugyanazt JOIN-oljuk (csak más relációba), az azért a MYSQL-nek illene kezelni.
Érzem, hogy lesz ebből valami.
Készülnek a táblák már. Le fogom próbálni mindet.
Sőt mivel partícionálni is szeretnénk a tulajdonságcsoportokra a kapcsolótáblát, lehet hogy még az is segítene kicsit.
Amúgy te az egyszerűbb, sűrűbben használt dolgokra, inkább a BIT alapú műveletet, vagy inkább azokra is ezt a módszert alkalmaznád?
És minden ötletre nyitott vagyok.
A másik kollega google valamije is felcsigázott.
Köszönöm tényleg ezeket a jó ötleteket mindannyiótoknak.
Nyilván, ha nem joinolsz,
Persze ez egy csomó csúnya dolgot von maga után: mennyire lesz ez karbantartható, mennyire jól lehet a későbbiekben az esetleges módosításokat megcsinálni, stb.
Ez a Google-s dolog engem is érdekel, most hirtelen a MapReduce jut eszembe, mit google algoritmus, de az nem tudom hogy jönne ide :)
Úgy, hogy a feladat tipikusan
Látom szeretnél inkább
Akkor kérem a következő segítséget.
(vagy a sok hóra tekintettel akár többet is. :))
Valóban
:-(
Akkor lehet, hogy én értettem
Amikor eljön az idő, hogy a látogató szeretné megtalálni a nagy Ő-t, és rátenyerel a Keresés gombra, a háttérben elindít n párhuzamos folyamatot, amelyek az egyes adatbázisokban külön-külön futnak, és a választ beteszik egy tömbbe, ami mehet ki a kliensre.
Ez nagyon hasonlóan működik a MySQL partícionáláshoz (vagy a shardinghoz), csak itt több a programozó kontrollja - és a felelőssége, bár arra még nem találtam információt, hogy az (mármint a partícionálás) többszálúan működik-e.
A MapReduce azt használja ki,
Állatorvosi ló: szavak megszámlálása szövegben. A szöveget feldaraboljuk, pl sorokra.
Map: a szöveget (szövegdarabot) áttranszformáljuk egy szó->előfordulás listára
Reduce: a kapott listákból aggregáljuk az előfordulásokat az egyes szavakhoz.
Az eredmény: szó->előfordulás lista, amit (nagyrészt) párhuzamosítva készítettünk el.
Igen
Próbállak kicsit majd utolérni titeket
Nagy nehezen létrejött a teszt környezet 2M-os ügyfélbázissal, és kicsit túllőve több mint 24M-os kapcsolótáblasorral.
Gyorsan utána olvastam. Tehát
Tehát vagy mint itt egy összetett feladat, amit valami MAP függvénynek kell átadni valamilyen módon, és az kisebb és kisebb részfeladatokra osztja a feladatot, majd a végén Reduce összeállítja a sok kis kapott részeredményt egy naggyá.
De igazából csak ennyit találtam még róla gyorsan.
Még azt sem, hogy mindez akár MYSQL-ben is elérhető -e, vagy ez más adatbázis-keresőt is igényelne...
Illetve ha a MYSQL is jó rá, akkor hol találni rá vagy gyakorlati példákat, vagy esetleg magyar leírást.
A mysql-nek partícionálás
A fájlrendszer szab korlátot
Ráér
MySql-ben is meg tudod valósítani, de konkrét példát nem tudok.
Gábor és Bence válaszaihoz nemigen tudok hozzátenni, talán annyit, hogy a kapcsolótábla nálam tuttira kétoszlopos lenne. És egyetlen attribútumtábla, tehát összesen 3-at kell "összeszorozni". A belső SELECT-et én nem ajánlom sok rekord esetén, mert az valóban Descart-szorzat lesz.
Köszi. Az lenne a kérdésem,
Az lenne a kérdésem, hogy ha csak egyetlen kapcsolótábla van, akkor mi határozza meg hogy melyik érték melyik tulajdonságcsoportba tartozik?
Úgy gondolod, hogy a BIT alapú értékeket kezeljük külön (ott fontos, hogy mindig kis értékek legyenek), és ezeknél a kapcsolótábla béli adatok pedig ne kerüljenek érték újra felhasznosítással...
Tehát ha azt mondom, hogy 112-es tulajdonságot veszi fel az ügyfél, akkor a 112-ből tudnom kell, hogy ez egy mozifilmet, és nem egy hobby-t jelent.
Cserébe a hiányzó mező helyett JOIN-olod az atributum táblát, hogy tudd, hogy melyik tulajdonságok azok amik össze tartoznak.
Jól értem?
Viszont azt is jól gondolom, hogy ekkor is vagy az én vagy a MadBence kollega által említett többszörös önmagára JOIN-olásra is szükség van, hogy a valódi OR és AND relációkat fel tudjuk írni? Vagy tévedek?
Esetleg nagyjából le tudnád írni mondjuk 2 vagy három tulajdonság esetén az SQL-t miként gondolod, mert ugye az attrubutumokat tartalmazó táblára a kapcsolótáblával van kapcsolata. és ha pl. MadBence kollega többszörös JOIN-van nézem, akkor kicsit érdekesebb lehet a reláció felállítása.
Köszi.
Megadod
Egyszerűen megadod egy másik mezőben.
kulcs,érték,típusPontosan
tulajdonságnévmezőt is, de ennél a kérdésnél ez nem számít.A kapcsolótáblát szerintem
Szerintem nem
A mi kiinduló állapotunk is ez
De mivel a film címét csak akkor kell tudnom, amikor mondjuk egy konkrét adatlapot jelenítek meg, így kereséskor nekem nem kell tudnom, hogy az milyen filmet, hobby-t, vagy mit is takar.
Így nincs Text alapú keresés.
Hát...
Értem és tiszteletben tartom
Csak azért írtam példát, vagy magyarázatot, mert ha valóban csak 3 tábla van, akkor még mindig nem értem hogy lesz belőle csoportok közti AND kiértékelés.
Első meglátással, én ebben az esetben még mindig azt látom, hogy ekkor az egyes tulajdonságok külön rekordonként jelennek meg, és akkor a sorok közti összefüggéseket kéne leírni.
Ha rosszul értem amit írsz, akkor javíts ki, de akkor én úgy látom, hogy ugyan oda jutok mint amiből mi indultunk ki csak mi 2 táblával(GROUP BY, GROUP_CONCAT, HAVING).
Ellenkező esetben egymást ütő WHERE feltételeket írunk le.
Nem szeretem ismételni magam
Ha az eddigi elméleti segítségek alapján nem tudod összehozni, akkor túl nagy fába vágtad a fejszédet.
Off
Hoppá, köszi
Szerk.: ezzel a Descartes-ossal légyszi üsd a fejem, ha még előfordulna! (Az emberi hibákért nem ér ütni! :))
Eredmény
Első körben azon, hogy hogyan tudom optimalizálni első körben a 24M-os tábla kelkérdezését.
Ez első optimalizálás után erre a végeredményre jutottam:
A kapcsolótábla továbbra is 3 mezős, annyi különbséggel, hogy az 'Attributum_id' mezőbe nem olyan kerül, hogy kedvenc film, hanem egy szám alapú mezővé alakítottam (tinyint).
Ez szerencsére azért nem okoz gondot, mert ezt a tulajdonságokat leíró táblában könnyen tudom jelezni majd az SQL-t előállító PHP függvények, hogy melyik tulajdonság milyen egyedi ID-ként van nyilvántartva.
Ezen kívül a 24M-ós kapcsolótáblát 16 partícióra bontottam fel. Minden egyes tulajdonság külön partícióra kerül. Így a legnagyobb partícióim sem tartalmaz 4-5M-nál több rekordot.
Ezen optimalizálással indultam meg a teljes tesztnek.
Két tulajdonságra szűrtem. Az egyikben 3 a másikban 2 tulajdonság érték szerepelt.
MadBence kollega megoldása nagyon szépen futott.
A mi elgondolásunk is szépen fut, de ellentmondásos eredményt ad.
Hogy mit értek rajta? Hogy bár a mysql azt írja, hogy futásidőben fele a mi megoldásunknak a futásideje, de néha meg mégis 105 szőröse. De ha a szerveren nézem, hogy mekkora terhelés, illetve, hogy a valóságban mennyi idő után kapom meg az eredményt, akkor minden féleképpen MadBence megoldása fut le jóval gyorsabban.
Tehát maradtam MadBence megoldásánál, viszont azt továbbgondoltam.
Először is a JOIN-ba írhatnánk további feltételeket a 'Attributum_id' tulajdonságra, így a WHERE-ben már csak 'Attributum_value_id'-ra kell szűrni.
Bár a fejlesztő szempontjából szebb lesz a WHERE kifejezés (kicsit olyan lesz mintha az egy nagy kapcsolótábla helyett minden tulajdonságra külön tábla lenne), viszont futás időben nem nyertem vele semmit.
Ezek után még tovább gondolkodtam ezt is:
Bár a mysql alapban is úgy néz ki, hogy nagyon jól tudja mely partíciókkal kell dolgoznia, de lehetőség van a partíciók kézi kijelölésére is. Mivel minden tulajdonság külön partíción helyezkedik el, így maga a partíció kijelölésével minden valós vizsgálat nélkül 'Attributum_id' feltételt ki lehet váltani.
Ezzel a módszerrel újabb 9-9.3% sebességnövekedést tudtam elérni.
A végleges variáció:
Ekkor ha tudjuk, hogy a 110-es, 150-es, és 530-sa azonosítója filmre keresünk rá, akkor nem kell még a mondjuk most 5M-ós particiót sem teljesen betölteni, csak annak egy kisebb darabját, ami a 100-199 és az 500-599 –es filmek kapcsolatát tartalmazza. Így lehet hogy csak 100-200e rekord között kell a mysql-nek keresnie az 5M helyett.
Hogy ezt hogy tudom pontosan megoldani, azt még nem kísérleteztem ki.
Tesztként én minden
PHP:
A keresésnél ha a felhasználó azt mondja, hogy legyen a szem szín kék vagy zöld, akkor a lekérdezéskor(select) nézi a szemszin táblát is.
Ha valakinek a profilját nézi valaki, akkor az users táblában látja, hogy milyen tulajdonságok vannak kitöltve, így a lekérdezések csak azokból a táblákból szed ki adatokat, ahol meg van adva valamilyen adat. Tehát ha a szeme színe meg van adva, akkor a szemszín táblában is belenéz és kiszedi a szükséges információt és megjeleníti. A táblák metszete is fontos, join-t vagy miket ajánlottak még.
Ha van 80 tulajdonság, az emberek mennyit töltenek ki? Ha van olyan adat, amit szinte mindenki kitölt, az mehetne egy vagy néhány táblába is.
De tulajdonságokat is lehetne csoportosítani. Például a tanulmányok során mehet egybe az általános, középiskola, egyetem, egyéb képzések. Kapna egy külön oszlopot erre.
Szerintem php szinten lehet érdemes leprogramozni, amit lehet. Magára a viselkedésre gondolok. Tehát ha állít bizonyos értéket az űrlapon, akkor azok alapján készít egy sql parancsot(feltételekkel is legenerálja), amit majd végre fog hajtani.
Hát...
Ha egyetlen formon kérsz 80 adatot, akkor szerintem egyet sem... Egyébként szerintem 5-10 között mozoghat az adatmennyiség, amit még könnyebben ki lehet töltetni. Efelett már "beszélgetni" kell a Júzerrel, pl. varázslószerűen, lépésenként haladva, és mindenképp tetszetősen, hogy érezze: ez őérte van.
Elég nehéz elérni a sok adat kitöltését, de nem lehetetlen.