Új hozzászólás Aktív témák
-
#1599
Sk8erPeter
nagyúr
pittbaba
#1596
Sk8erPeter
nagyúr
válasz
pittbaba
#1596
üzenetére
látatlanban: indexelés, EXPLAIN?
Szerk.: ja, most látom, hogy írtad is (csak átfutottam a kérdéseden):
"Olvastam, hogy létre lehet hozni index-et a tábla oszlopaihoz, milyen oszlopokhoz érdemes?"
legalább próbálkozz egy EXPLAIN-nel. Na de konkrétabbat majd ha esetleg kipróbáltam (és elolvastam normálisan a kérdésedet![;]](//cdn.rios.hu/dl/s/v1.gif)
), meg ha addig nem érkezik másik válasz.[ Szerkesztve ]
Sk8erPeter
-
modder
aktív tag
válasz
pittbaba
#1596
üzenetére
kíváncsiságból én is feltettem notin mysql-be betöltöttem, és elkezdtem futtatni a lekérdezésedet. Nem tudtam végigvárni
1) Ez egy viszonylag normalizált adathalmaz, de nem feltétlenül lesz jó keresések szempontjából
2) mivel az adatbázis csak lekérdezésre lesz használva, simán megcsinálhatod, hogy létrehozol különböző táblákat, amik a keresést megkönnyítik: a relációkat denormalizálod, azaz egy adat, és az adatok közötti függőségek többszörösen lesznek letárolva. Ezzel azt nyered, hogy gyorsabban fogsz tudni keresni.
A "csak lekérdezéseket" azért emeltem ki, mert így nem kell majd azzal foglalkoznod, hogy az újonnan beszúrt adatok elrontják-e az adatbázist. Például az egyik táblába beszúrod, de a másikba elfelejted, satöbbi.3) Igen, az indexelés, amit már említettek. A megálló nevére rá kell tenned egy indexet mindenképpen, ha így keresel, viszont nem hiszem, hogy SQLiteban van fulltext index: azaz szövegen belüli indexelés. Mivel LIKE %valami%-kal keresel, mindenképpen be fogja olvasni a rekordot, belemászik a szövegbe, és rákeres a szövegrészletre, szóval hiába indexeled, semmit nem fog érni. Max akkor, ha tökéletes egyezést keresel.
Többi indexelés:
-- stops.stop_id elsődleges kulcs, állítsd be annak, vagy tegyél rá egy indexet;
-- stop_times.trip_id -ra és stop_times.stop_id -ra külön-külön tegyél egy indexet;
-- trips.trip_id -ra tegyél egy indexet
azért ezekre, mert ezek azok az oszlopok, amik mentén te joinolsz, és ezek által az id-k alapján fog keresni az adatbázis.ezekkel az indexekkel nekem a keresés 3 másodperc alatt lefutott laptopon. telefonon nem tudom milyen lesz...
Konkrétizálva:
Te most azt csinálod, hogy rákeresel a megálló nevére, majd megálló id alapján groupolod egy másik tábla oszlopa alapján, hogy ha egy utcában több megálló is van, azokat is megkapd. Ezzel még csak a megállókat találod meg, de nem nyersz információt az áthaladó trippekről. Az információ amire valójában szükséged van az a megálló_neve, megálló_id.Ehhez képest te összejoinolod mind a három táblát. tegyük fel, hogy a stops táblából megtalálja a megfelelő sorokat amik pl az Örs vezér tere-re stimmelnek, ez lesz pl 10 sor, akkor szépen elkezdi végigkeresni a másik két táblában a hozzájuk tartozó sorokat, de a trip_id és a departure_time nem fog információt hordozni, mert groupoltad az egészet stop_id alapján, szóval egy megállóhoz csak egy trip_id-t fog kiadni... szóval ez így teljesen rossz.
Ami neked kell ebben az esetben:
SELECT stop_name,stops.stop_id FROM stops WHERE stops.stop_name LIKE '%Örs%' GROUP BY stops.stop_idViszont te GPS alapján akarod megtalálni, ez is jó lesz. Indexelned kell a stop_lat és stop_lon mezőket, és lekérdezed úgy, ahogy írtad. Csak a megállót! Akkor felhasználó kiválasztja a pontos megállót, így az átmenő trip-eket már csak az alapján kell keresned (fölösleges a közelben lévő megállókon átmenő tripeket is megkeresni).
Átmenő trippek:
SELECT * FROM `stop_times` WHERE stop_times.stop_id = valami and `departure_time` > "06:00:00"Optimalizálás:
Visszatérve ahhoz, amit a legelején írtam a denormalizálásról. Nekem ezek a lekérdezések a belinkelt adathalmazon elég gyorsan lefutottak, de telefonon lehet, hogy más lesz a helyzet. Először próbáld ki rendesen indexekkel, hogy milyen eredményeket kapsz, és ha nem jók, akkor el lehet kezdeni szétdarabolni pl. a stops táblát 10 külön táblára, ezt hívják particionálásnak. Feltéve, hogy az SQLite van olyan intelligens, hogy nem olvassa be a memóriába az egész adatbázis fájlt, hanem beindexeli hogy melyik tábla hol kezdőik a fájlban, ezzel rendesen meg tudod gyorsítani a dolgokat.Például szétdarabolod a stops táblát 10 táblára a stop_name szerint egyenletes eloszlásban, alfabetikusan növekvő sorrendben. Amikor név alapján akar keresi, tudni fogod egyből, hogy melyik táblában keress, mert tudod, hogy melyik tábla mettől meddig tartalmaz stop_name elemeket. Ez ugye csak név alapján keresésnél hasznos, úgy ha a felhasználótól elvárod, hogy a begépelt megállónévnek az eleje egyezzen valamelyik megállónévvel.
A stop_times feldarabolása. Mivel ez a tábla rohadt nagy, ezzel is tudsz nyerni időt. Én a stop_id alapán darabolnám fel szintén alfabetikusan hasonlóan ahogy az előbb írtam, el tudod dönteni már a stop_id alapján, hogy melyik táblában keress.
miért stops.stop_name és stop_times.stop_id alapján darabolj, és ne pl stops.stop_lan és stop_times.departure_time alapján? Azért mert az SQLite valószínűleg BTREE indexelést alkalmaz, ami nagyon jó intervallum keresésre. Alkalmazható számon, dátumon, talán még szövegen is! Viszont nem találtam , hogy lenne SQLiteban hash indexelés, ami arra jó, ha szeretnél megtalálni egy elemet konkrét értékkel. Ilyen az, amikor id-k alapján joinolsz. Amikor meg akarod keresni az adott stop_id-n áthaladó járatokat a stop_times táblában adott időponton belül, akkor megkönnyíted az adatbáziskezelő dolgát azzal, ha a particionálással 200e sor helyett csak 20e-ben kell keresnie.
Persze ez csak elmélet. az egész attól is függ, hogy a stop_times táblában először intervallumkeresést hajt végre az időpontra, és utána választja ki stop_id alapján a sorokat, vagy fordítva, de ezt már nem tudom, ki kell próbálni.
Azért jó lehetőség ez az adathalmaz optimalizálásra, mert nincsenek komplikált lekérdezések, csak egy-két féle, így ki lehet alakítani úgy a struktúrát, hogy ezeknek teljesen megfeleljen. Még ami eszembe jutott, hogy az adatbázisok általában elsődleges kulcs szerint teszik sorrendbe az adatokat fizikailag. Szóval ha a stop_times táblában (stop_id, trip_id) -t adsz meg elsődleges kulcsnak ebben a sorrendben, akkor megegyszerűsítheted az adatbáziskezelő dolgát, amikor stop_id alapján keresel, mert az ugyanahhoz a stop_id-hoz tartozó rekordok egymás mögött lesznek elhelyezkede, tehát szekvenciálisan gyorsan ki tudja olvasni az összeset.
De amúgy az is lehet, hogy simán az indexekkel tökéletesen gyorsan fog működni telefonon is, és az egész novella, amit leírtam teljesen fölösleges
![;]](http://cdn.rios.hu/dl/s/v1.gif)
), meg ha addig nem érkezik másik válasz.
