optimalizace dotazů a struktury tabulek

Ahoj,

po dlouhé době jsem sem zavítal, a prosím Vás o radu. Už asi 4 dny řeším rychlost dotazů, optimalizaci a rychlost skriptů obecně, no a zde vzniklo pár témat, se kterýma bych se rád poradil

INDEXY :
otázka zní, do jaké míry je používat, aby to zrychlilo tahání dat pro konkrétní dotaz ale nezpomalilo komunikaci s tabulkou obecně. Řekněme tabulka články:

`date` - podle data se sestupně řadí, takže index (navíc je to v podmínce pro generování archívů)
`active` - nabývá hodnot 1/0, jeho účel je asi jasný - v každé podmínce je WHERE active = '1', takže tam mám index také.
`category_id`- relace s tabulkou kategorie, index je jasný...
`seo_url` - unique, což už je další klíč

no a otázka - ano/ne ? jsou některé klíče zbytečné, nebo je naopak dáváte na úplně jiné typové sloupce?

FULLTEXT :
že bych si o tom přečetl málo se opravdu říct nedá, zkoušel jsem ale několik kombinací seskládání dotazu, a vždy jsem narazil na něco, co se mi tak úplně nelíbilo... proto bych se rád zeptal, kterou z možností používáte vy:

1) MATCH(name) AGAINST('{$_GET['q']}' IN BOOLEAN MODE) OR
MATCH(text,) AGAINST('{$_GET['q']}' IN BOOLEAN MODE) OR MATCH(anotation) AGAINST('{$_GET['q']}' IN BOOLEAN MODE)
kde je INDEX vytovřenej jako
a) FULLTEXT KEY `search` (`name`,`text`,`anotation`)
b) FULLTEXT KEY `name` (`name`), FULLTEXT KEY `text` (`text`), FULLTEXT KEY `anotation` (`anotation`),

2) nebo MATCH(name,text,anotation) AGAINST('{$_GET['q']}' IN BOOLEAN MODE)
kde je INDEX vytvořenej jako
a) viz 1
b) viz 1

přičemž co si pamatuji, pouze jedna z těchto možností mi vracela skore, podle ktereho jsem mohl sortovat - i tak jsem ale zvolil 2a, právě kvůli tomu, že dotaz trvá zhruba stejně dlouho jako např. obyčejný výběr posledních článků - a vrací, to co má - už nevím kde, ale u jedné z možností mi nefungovaly operátory jako např. minus

PŘIDÁVÁNÍ A MAZÁNÍ INDEXU :
Otázka je jednoduchá, a to "proč"? , když je to tak zoufale pomalý. Říká se, že tabulka s fulltextovýma indexama je časově náročnější na vkládání dat, ale když se nad tím člověk zamyslí, je lepší zpříjemnit uživatelům vyhledávání nebo zpříjemnit sám sobě vkládání nových položek v administraci?

Díky

Resi to spousta lidi a nikdo nevi, co je nejlepsi :) (Já to taky nevím..)

Ale osobně:
indexy: mel bych indexy pouze 3 - category_id a date. Indexovat active mi prijde zbytecne (pokud jsem alespon dobre pochopil strukturu tabulky:). A k tomu samozrejme ten seo_url unique (ten ma lepsi vyuziti nez klasicky index a je tedy bez debat :))
fulltext: Osobně jsem vzdy vyuzival moznost 2a (z mych zkusebnich testu (sice na dost odlisne tabulce) vypadala nejlip:)) - [ale vyvaroval bych se primeho $_GET['q'] v dotazu :) - pokud neni alespon nejak predem upravovane a neni dobre kontrolovan obsah..:)]
pridavani indexu: odpoved na dvou otazku je jednoducha.. hodi se zrychlit to, co se dela nejcasteji.. (a urcite se casteji hleda, nez pridavaji nove polozky:))

Mike
Obecne
`active`
... kolik je dat pro active=1 a 0? Mozna by se vyplatilo udelat 2 tabulky.
Pokud bych to pouzil jako index, pouzil bych to jako jednu z prvnich podminek.
Prvni podminku bych pouzil takovou, aby se vyradilo co nejvic dat na co nejmene narocnou operaci.

`date` - podle data se sestupně řadí, takže index (navíc je to v podmínce pro generování archívů)
... zajimave, pokud je neco ORDER, tak bych na to index nedaval. Predpokladam, ze databaze funguje tak, ze vybere zadane sloupce a sloupce v ORDER do pomocne tabulky podle WHERE a vysledek az nasledne serazuje. Takze si myslim, ze index na to nema vliv.
Ale mozna se pletu a zrychli o serazeni.
... Neco jineho je, kdyby jsi date pouzil ve WHERE.

`category_id`- relace s tabulkou kategorie, index je jasný...
...bez komentare, v podstave, mozna bych zkusil pole SET a kategorie tim prevest na cisla, pripadne kategorie vypsal do tabulky sql nebo php. Proste pouzil cisla. Predpokladam, ze kategorii nebude moc.
Pak by byla moznost vyhledavat kategorii jen podle prvnich 3 znaku, pro urychleni. Cili bych dal neco jako WHERE kategorie LIKE 'abc' AND kategorie='abcdlouhynazev'. Ale nemam vyzkouseno, myslim si, ze by to urychlilo hledani.

`seo_url` - unique, což už je další klíč
... bez komentare, hlavne nevim, co to pole muze obsahovat za data

Fulltext, s tim jsem zatim prilis nepracoval. Ale uz jsem videl programy, ktere vytvareji vlastni indexove tabulky, protoze mysql indexy byli pomale. A ty same programy delali indexy nad indexy. Vyznamne tim zrychlili hledani, protoze se neprohledavala tabulka, ani indexy tabulky, ale pomocne indexy, ktere byli ocesane treba pro konkretni dotaz.
Dalsi mozny zpusob je data proste rozdelit do vice tabulek se stejnymi sloupci a udelat spesl tabulku, ktera dela pro vsechny spolecne indexy. Pripadne z kazde tabulky vlastni indexy a nejak sikovne vyhledavat vzdy v te spravne nebo vsechny postupne.
Myslim, ze jsem nekde cetl, ze google ma indexy rozhazene na nekolik pocitacu a nadelanych nekolik kopii a vzdy si vybere Pc s nej pripojenim pro vyhledavani

Cili hodne zalezi na tom, jaka jsou data tabulek a podle toho se da mnoho prizpusobit.

Vetsina lidi neumi udelat spravne indexy a vyhodnotit situaci, co se bude hledat nejcasteji a jak tomu napomoci. Pak je jakekoliv reseni pomale.

Pánové děkuji za odpověď.

=== Freeze ===
se sloupečkem active máš pravdu, i jeden kolega mi radil, že pokud nemá příliš odlišných hodnot je index zbytečný.

pro fulltext jsem nakonec po dlouhém testování zvolil také 2a, spolu ještě s přídáním počítání score.

pridavani indexu - naprosto souhlasim :) jen mě zmátly všechny ty tutoriály, kde autoři zmateně přidávájí a odebírají indexy...

=== peta ===
active:
no nevím. pokud bych to měl rozdělené do dvou tabulek, znamenalo by to téměř zdojnásobit tabulky, které mám navržené nyní : články, kategorie, uživatelé, fotogalerie atd... navíc jak potom řešit, když uživatel zneaktivní článek? přesunout ho z tabulky do tabulky? zde bych určitě nevytvářel další tabulky...

date:
testoval jsem, mírné zrychlení bylo znát. ve WHERE samozřejmě date nemám, nepočítám-li generování archívů, ale tam to nijak neřeším.

category_id:
tento bod je právě sporný :) klíč jsem umístil za účelem toho, abych zrychlil výběr článků pro jednu danou kategorii. pokud počítáme v průměru 10 kategorii na průměrný blog, už je to trochu větší rozmezí čísel a index jsem přidal. tady narozdíl od archívů se optimalizovat snažím, přecijen kateogorie jsou důležitější položkou blogu než archívy. akorát jsem nepochopil, na co narážíš tím WHERE kategorie LIKE - nic takového používat neplánuji, koncepci pro detail kategorie mám ve stylu SELECT ... WHERE ... AND category_id = '2' atd ...

seo_url:
prostě to, co je v url. http://neco.cz/toto-je-nadpis-clanku-a-tento-retezec-je-ulozen-ve-sloupecku-seo-url/ ač mám v dotazu na detail článku vždy LIMIT 1, přecijen chci ošetřit duplicitu předem.

fulltext:
díky za námět, zkusím si na toto téma něco vyhledat a nastudovat.

category_id
kategorie, pokud mas text, tak jsou uspornejsi cisla. Cisla ziskas treba pres pole SET, kde se to nejak sikovne indexuje podle textu i cisla. Ale myslim, ze je to nejak omezene, proto se to nepouziva.
Druha moznost je prevest do externi tabulky seznam kategorii.
A treti moznost je vyhledavat textem, ale index omezit treba na prvni 3 znaky. Pomoci LIKE se dostanes na seznam nejblizsich a pres rovnitko to pak vytridis. Otazka je, kolik nejblizsich tam bude a jestli je to tak rychlejsi nez resit index bez omezeni delky textu. Netestovano.