Dotaz: Indexy - prosím o radu

[Petr]

Mám v databázi MySQL (verze 4.1) tabulku Chron, která nyní má cca 900 000 řádků (70MB). Jsou vytvořeny tři indexy (primary plus dva další). Tabulka obsahuje mimojiné pole Zkratka Varchar(5) a Text1 Varchar(25). Tato pole nejsou indexovaná, ale občas se podle nich vyhledává a vytížení procesoru na serveru je 100% a trvá to dlouho. Když vytvořím indexy pro tato pole, zrychlí se vyhledávání, ale zřejmě se zpomalí insert (a update a delete). A to mě zajímá. Uživatelé vkládají jednotlivé záznamy - poznají při tomto "ručním" přidávání ono zpomalení nebo se to výrazně neprojeví?

S tím souvisí i můj druhý dotaz: donedávna byla na serveru (mnohem horším) verze MySQL 3.23 a nikdy nebyl problém s tímto vyhledáváním (tj. nebyl procesor vytížen tak, že ostatní uživatelé nemohli pracovat). Aplikace se nezměnily, pořád ty samé dotazy, velikost databáze zhruba stejná.

Odpovědi

[svatekr]

Určitě indexovat. Vyhledávání se zkrátí z řádu sekund na milisekundy. Při vkládání to uživatelé nepoznají. Snad pokud by importovali najednou více záznamů, ale po jednom je to neměřitelné.

[Petr]

Díky za odpověď. Je lepší udělat ten index pro pole Text1 (jsou v něm převážně čísla faktur, variabilní symboly, atd) indexovat jen pro prvních řekněme osm znaků nebo je to (zhruba) jedno?

[cronin]

Zalezi od udajov, od select-u, od mnohych veci. Uvedom si, ze indexy sa vzdy navrhuju pre konkretnu aplikaciu, este presnejsie pre konkretny select. Niekedy moze byt vhodny normalny index, niekedy reverzny, niekedy index na funkciu nad retazcom. Stretol som sa s aplikaciou, kde najvhodnejsi index bol (jedna sa o postgresql, ale na ilustraciu to staci):

create index idx_xxx on sl (length(yyy), yyy);

kde yyy je stlpec typu varchar(129). Proste prasacina, ktoru by len tak s fleku nikto nenavrhol, ale pre dany select bola najucinejsia. Velke obmedzenie pri indexoch je, co vlastne ktora db umoznuje. PostgreSQL so svojimi selektivnymi indexami a moznostou pouzivat priamo v indexoch funkcie je pekne vypecene.

Len som chcel povedat, ze ako konkretne vyrobit indexy, na to musis prist sam. Neda sa to poradit vo vseobecnosti.

[Petr]

já vím. ale nevím, jaký je v praxi rozdíl mezi indexem pro prvních 8 znaků a pro celé pole (v tomto případě v rychlosti selectu asi malý, ale ve velikosti (MB)?) Předpokládám, že těch osm znaků by zde mělo stačit ...

[cronin]

Vyskusaj a uvidis! Mozno bude rozdiel nemeratelny, mozno dramaticky. Zalezi to aj od samotnych udajov. Co ak budu mat prvych 10 znakov vsetky zaznamy takmer rovnakych? Co ak ...

[Ľubomír Host]

ak je to 900 tis. zaznamov a daju sa tieto zaznamy podelit na nejake casove obdobia, tak mozno by stalo za to upgradnut aplikaciu, aby:
- sa zaznamy vkladali do jednej tabulky
- statistiky/vyhladavania robili nad tabulkami typu MERGE
- zaznamy z predchadzajuceho mesiaca stale presunut do tabulky tabulka_2006_10 Alebo mozno skusit pouzit vo vyhladavani UNION nad niekolkymi tabulkami (za kazdy mesiac) a tabulky poctivo naindexovat Alebo kupit tucnejsie zelezo a iba naindexovat tu jednu velku tabulku.

[cronin]

Z coho plynie jednoznacna odpoved na polozenu otazku "to index or not to index?": indexovat.

[cronin]

Skus forum na databazovom svete.

Inac v serioznej databaze by vsetky akcie mali byt meratelne, co sa tyka vykonnosti. Ma MySQL nieco ako EXPLAIN ANALYZE? Ak ma transakcie, tak by mohol byt testovatelny aj ten insert/delete:

begin; 
explain analyze insert into ... ; 
rollback;

S pravdepodobnostou hraniciacou s istotou budu vysledky v prospech indexovania. Navrhnut dobre indexy (zlozene, pouzivajuce funkcie, etc.) nie je jednoduche, zvycajne to vyzaduje dobru podporu zo strany DB, skvelu znalost administratora/developera a pohratie sa s realnymi dotazmi v spravnom tvare (pozor na prepared statements). Vysledok vsak zvycajne stoji za to.

[LesTR]

Zdravim,
mysql samozrejme ma explain (viz. Doc )
Transakce umi, ale pouze nad tabulkami typu InnoDB a jeste te jedne exoticke, ktere ted nemohu prijit na jmeno.
S tabulkami InnoDB je ovsem problem, ze ve vetsine pripadu sou mnohem pomalejsi nez Myissam.
Typ Myissam byl primarne vyvyjen jako mala, rychla databaze, coz je.
V tabulkach o vetsim poctu radku je mnohem rychlejsi pouziti pevnych delek zaznamu, tj. misto varchar(20) pouzit char(20), coz pomuze rychlosti, ale zase to sezere vice mista na disku.
Indexy jsou nutnosti a jak tu jiz nekdo zminil, neni mozne rici, udelej to takto a takto, bez znalosti dat v DB a dotazu do ni.
Osobne si ale myslim, ze vytvaret index na poli typu text, je ten nejvetsi hnus co muze byt, tfuj tfuj tfuj... P.S. Tuto reakci jsem zaradil pod vas prispevek, protoze jsem odpovidal na explain a transakce. Jak to tak ale byva, clovek se rozepise vice : )

[happy barney]

myisam však nepodporuje row lock, iba table lock, čo napr pri častých insert/update výrazne spomaľuje.

[hm]

...neboli na pořádnou práci je lepší _skutečná_ databáze

[Ladicek]

mysql samozrejme ma explain – ale jenom na selecty…