MySQL in iskanje po posameznih črkah

philips · 16. jun 2008

Narediti moram iskalnik po geslih za križanke. Tabela bo vsebovala id, geslo, št_črk.
Po št_črk bom tudi indeksiral, da bo iskanje malo hitrejše.

V splošnem bo iskanje zgledalo tako: podal boš dolžino besede in posamezne črke, recimo druga črka L, zadnja R, dolžina 5; in potem bi ti naj vrnilo ALTER, pa mogoče še kakšno ustrezno geslo.

Zdaj se še odločam, ali bi bilo polje geslo TINYTEXT ali pa TINYBLOB. Pri prvem bi potem delal substringe in jih primerjal z ustreznimi črkami. Z BLOB pa še nisem nikoli delal, tako da bi si mogel še malo pogledati kako je tam (vem edino da so not surovi bajti). Je sploh kakšna razlika v hitrosti pri iskanju ? Ker po bajtih bi naj hitreje iskal, ampak text je v končni fazi tudi z nulami pa enkami zapisan

Predlaga kdo kakšno boljšo metodo, mogoče samo kakšno optimizacijo ? Zapisov v bazi bo verjetno par 10 000.

Bossek · 16. jun 2008

full text index
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

philips · 17. jun 2008

Kaj pa če ne bi rad indeksiral po samem tekstu ?

doto · 18. jun 2008

Če imaš znane začetne ali končne črke v besedi, si lahko pomagaš že z wildcardi ( beseda like 'al%' ali beseda like '%r').

Če pa je prioriteta res brzina, pa naredi tako, da bo vsaka črka v svojem polju tipa CHAR(1) in na vsa polja daj indekse. Za iskanje pa si potem narediš wraper class, ki ti avtomatsko generira query-e in nazaj lepi skupaj rezultate.

philips · 18. jun 2008

Za vsako črko bom bolj težko delal svoje polje, ker so gesla lahko različno dolga.
Saj posamezne črke lahko s SUBSTR(geslo, 4, 1) = 'L' primerjam. Samo sem mislil če je kakšna boljša (beri: hitrejša) metoda.

jurek1973 · 18. jun 2008

Najdaljša beseda v SSKJ je baje "prestolonaslednica" (18 znakov).
Torej za rezervo naredi v bazi 30 polj CHAR(1), z imeni recimo crka1 .... crka30.
Pa index na vsako polje, potem pa že narediš select po polju recimo crka4="L"
v končni tabeli pa sestaviš nazaj besedo crka1+crka2+crka3+...+crka30

doto · 19. jun 2008

Problem funkcij tipa substr je v tem da s tem izgubiš prednosti indeksiranja, kar se zelo pozna na performancah in v končni fazi na obremenjenosti baze.

futuristic · 19. jun 2008

Iskanje po substringih nabrž res ni najboljša varinata. Eno zaradi počasnosti, drugo pa kako boš našel "APOLON" če ti manjkata "L" in "N":
V T-SQL bi npr. uporabil: where naziv like 'APO_O_'

Podčrtaj namreč nadomesti en znak, kako je pa v mysql pa žal ne vem.

Popravek. Sem preveril in tudi mysql ima podčrtaj za nadomestitev enega znaka:
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

philips · 19. jun 2008

Boma kar podčrtaj uporabil, zgleda dokaj kul zadeva. Hvala

Z bazami se nisem nikoli kaj preveč ukvarjal, tako da ne poznam vseh funkcij in trikov.

Kako pa mi glede indeksiranja priporočate ? Zaenkrat imam v mislih samo po številu znakov, po samem geslu pa ne bi (potem bi že indeks bil zelo velik). Saj je MySQL tako pameten, da bo prvo šel filtrirat po številu znakov, potem pa še šel črke primerjat, ne ?

doto · 19. jun 2008

Na oba daj index. Posebej je pomembno, da ga daš na geslo. Za dolžino pa moraš v sql-u dodati pogoj "where dolzina = n". S tem zmanjšaš nabor vrstic, ki se potem filtrirajo s pravilom za geslo. Na ta način malo pridobiš na hitrosti.

philips · 19. jun 2008

Bom si vzel en dan malo časa, pa nafilal ene 100k random stringov, potem pa bom malo testiral z in brez indeksa na geslu. Če bo zelo velika razlika, bom pač še tam imel indeks

philips · 19. jun 2008

No, pa sem si vzel čas

Tabelo sem nafilal s 100k unique random stringi, dolžina je bila med 4 in 20. Polje geslo je bilo VARCHAR(50), polje dolzina pa TINYINT (to polje je bilo ves čas indeksirano).
SQL stavki so bili takšne oblike:

Koda:

SELECT * FROM gesla WHERE dolzina = 15 AND geslo LIKE '__x______n__lu_';

Rezultati pa so precej zanimivi

(povsod je bilo 1000 iskanj, nato pa izračunano povprečje glede na en query)
geslo je bilo nastavljeno na unique: 53.8996109962ms
na geslu ni bilo indeksa: 62.499614954ms
index na geslu: 53.2832129002ms

nato pa sem šel še testirat, če mu ne podam dolžine, torej SQL oblike:

Koda:

SELECT * FROM gesla WHERE geslo LIKE '__x______n__lu_';

brez indeksa na geslu: 65.7569129467ms
indeks na geslu: 54.2530810833ms

Zdaj pa mi najbolj ni jasno, zakaj je tako majhna razlika med tistim brez podane dolžine in s podano dolžino (pri obeh je indeksirano po geslu). Ker 1ms je res zelo majhna razlika, ki je mogoče rezultat premajhnega števila iskanj

Pa tudi tam kjer na geslu ni bilo indexa, je razlika le ~3ms.

Ali on sploh prvo skrči nabor glede na dolžino, pa komaj potem geslo primerja ? Iz meritev glih ni razvidno

Iskanje

Iskanje

MySQL in iskanje po posameznih črkah

philips

Guru

Bossek

Pripravnik

philips

Guru

doto

Fizikalc

philips

Guru

jurek1973

Guru

doto

Fizikalc

futuristic

geđet frik

philips

Guru

doto

Fizikalc

philips

Guru

philips

Guru

Podobne teme