MySQL(database), Indeksering av tabeller?

[Simon Zimmermann]

Hei, jeg har for første gang nå lagd en liten blogg med PHP og MySQL, og alt fungerer egentlig som det skal.

Siden dette er første gangen jeg jobber med database så leste jeg litt på FAQ-en på websidene til webhotellet mitt.

Der skriver de at jeg skal Indeksere tabeller jeg lager og at at det vil gå opp til 100-200 ganger raskere når man gjør det. De skriver også at det er et must å gjøre dette, og etter å ha lett litt etter informasjon på internett så skjønner jeg jo at det er bra.

 

REG. Leste litt rundt temaet og fant ut jeg ikke lurer på noe lenger allikevel ...

Takk for hjelpen

Endret 15. oktober 2004 av Waldmeister

[Skaptrollet]

hmm er det seriøst så effektivt å indeksere ? hmm virker litt rart

[Torbjørn]

WaBBiT: sammenlign med et indeksert register bakest i en bok.. det går noe kjappere å slå opp der enn å bla gjennom boka etter søkeord.

[Skaptrollet]

høres jo egentlig fornuftig ut, men hvorfor har jeg ikke hørt om det før? hmm... hvorfor lages det ikke index automatisk i mysql hvis det er så ekstremt mye bedre? det kan jo ikke bare være bra? noe må jo ta lenger tid elns hvis du indekserer siden det ikke blir gjort automatisk mener jeg?

 

håper ikke jeg driter meg ut nå...

[Nervetattoo]

Indeksering tar ekstra disk plass.

Litt vanskelig og indeksere automatisk når mysql ikke vet hva du vil indeksere.

Du tjener ingen ting på å indeksere et felt du aldri søker i.

Du kan heller ikke indeksere felt som text.

[Torbjørn]

"ikke indeksere felt som text"?

 

kan du utdype den litt, og gjerne forklare at du ikke snakker om indekseringen forbundet med full text search for eksempel

[????????]

Tror dette er en klassisk grunn for at jeg anbefaler at folk leser bøker i steden for private tutorials. Hvis du mener felttypen TEXT så kan en, som Torbjør korrekt påpeker indeksere ved hjelp av FULLTEXT (ALTER TABLE tabell ADD FULLTEXT(felt)). Ander "text" felter kan en indeksere på vanlig måte.

 

Videre så virker det på mange som valget er å indeksere eller ikke indeksere. Noe som kan være vanslig å få med seg når en bare leser tutorials på nettet er at en behøver ikke å indeksere hele feltet. Skal slutte å kritisere online tutorials - det finnes mange bra også. Skal en indeksere f.eks. feltet brukernavn så hjeler det mye hvis en indekserer de fire første tegnene.

 

Når det gjelder hvor mye tid det er mulig å spare på å indeksere så er det avhengig av mye. Først og fremst størrelsen på databasen og typen indeksering.

[Simon Zimmermann]

Her er en norsk side jeg fant som skriver litt om å indeksere innholdet i en MySQL-tabell Jonepet.com - Om indeksering

 

@????????: Skjønner godt at det er bedre å lese bøker om forskjellige temaer enn å finne informasjonen på private nettsider, men for meg så skal det litt mer interesse til enn et par spørsmål rundt temaet for å faktisk gå i butikken å kjøpe en bok. Det er nemlig sånn at faglitteratur (i mine øyne) er meget dyrt, men det skal også sies at den neste boken i min "kjøpeplan" omhandler MySQL og PHP.

 

Forresten så ble den tråden her litt dum, jeg burde å søkt mer rundt på internett før jeg den ...

[????????]

I den artikelen står det en tidsbeparelse på 0.04 sekunder, fra 0.06 til 0.02 - dette er en besparelse på 1/3 eller 66.66667% - ikke til å forveksle med motsatt forhold som vil være 300%. Scriptet sparer litt tid, men ikke 300%.

 

[edit]

så at det var web10 du siktet til og ikke artikelen over

 

En korrekt indekseret database vil være 100-200 gange hurtigere ved søgninger end en ikke indekseret.

Denne påstanden er så ekstrem at den rett og slett virker useriøs. Det vil i noen tilfeller være mulig, men det er meget skjelden! I tilfeller hvor et felt kan gjøres unikt og databasen er stor så finnes det nok en del tilfeller. De fleste databaser på et slik webhotell er neppe så store. Missforstå ikke, det er absolutt mye å hente på å indeksere databaser![/edit]

 

Selvfølgelig er innkjøp av PHP bøker en vurdering alle må gjøre selv. Glem ikke at noen bibliotek har PHP bøker.

Endret 17. oktober 2004 av ????????

[Nervetattoo]

"ikke indeksere felt som text"?

 

kan du utdype den litt, og gjerne forklare at du ikke snakker om indekseringen forbundet med full text search for eksempel

Det jeg mener er et vanlig text felt ikke kan indekseres.

 

ALTER TABLE tabell ADD INDEX ( text_felt )

 

Vil returnere feil:

#1170 - BLOB column 'content' used in key specification without a key length

[christt]

WaBBiT: sammenlign med et indeksert register bakest i en bok.. det går noe kjappere å slå opp der enn å bla gjennom boka etter søkeord.

Grei sammenligning, men det er viktig å få med at indeksene lagres som et binært-tree og det er derfor utrolig raskt å finne det du leter etter ved bruk av indeks.

 

I en tabell med 10 rader vil ikke dette utgjøre noen forskjell, med når man snakker om flere tusen eller millioner rader blir riktig indeks alfa og omega.

[????????]

"ikke indeksere felt som text"?

 

kan du utdype den litt, og gjerne forklare at du ikke snakker om indekseringen forbundet med full text search for eksempel

Det jeg mener er et vanlig text felt ikke kan indekseres.

 

ALTER TABLE tabell ADD INDEX ( text_felt )

 

Vil returnere feil:

#1170 - BLOB column 'content' used in key specification without a key length

Du skal ikke bruke

ALTER TABLE tabell ADD INDEX ( text_felt )

- hvis du tar en titt på det jeg skrev. Du må bruke:

ALTER TABLE tabell ADD FULLTEXT(felt)

[knatten]

Angående dette med hvor mye som er å spare på en index. Til de av dere som kan noe matematikk:

Uten index vil mysql søke gjennom alle radene i databasen. Ved n rader tar dette n tid.

Med index vil mysql søke gjennom logaritmen til antall rader i databasen. Ved n rader tar dette log(n) tid. (Vi bruker her en 2er-logaritme)

 

Eksempelvis:

n=64 log(n)=6

n=1024 log(n)=10

n=32768 log(n))15

n=1048576 log(n)=20

 

Som dere ser har man mer å spare jo større antall rader er. Når antall rader øker vil man raskt spare mye mer enn 100-200 ganger som er nevnt over.

 

De som er interessert i å vite mer om kjøretidsutvikling og slike ting, anbefales å kikke litt på binære trær, O-notasjon osv.

[????????]

Dette er en god begynnelse på forklaring henting av all data, men du nevner lite om indekseringen. Det er ikke slik at MySQL vet hvilke rader den skal finne dersom du indekserer. En viktig del av MySQL er dens event til å gjette. Den gjetter hvor mange rader den kommer til å trenge for å så søke gjennom det området hvor de kan ligge.

 

Eksempelet til Torbjørn er meget bra. Tar vi utgangspunktet i en B-Tree index så kan en for enkelhetsskyld si at mysql sorterer data slik at den lettere vet hvor den skal søke. Har en indeksert første tegnet så stemmer det faktisk meget bra med eksemplifiseringen til Torbjørn. Søkere en da etter "eple" så vet mysql at "f...." er for stort og "d...." er for lite. Da har den for enkelthetsskyld begrenset seg til å søke gjennom de som er sortert som "e". På lignende måte kan en si at fordelen øker ved å ta med flere tegn i indekseringen. Dette er en del forenklet, og ser bort fra nødvenige prosesser fra mysql som query optimizer, åpning og lukking av tabeller, søking i minnet og så videre. Den gir i alle fall et innblikk.

 

Når det gjelder hvor mye det er å tjene på å indeksere så er det som knatten sier, helt avhengig av tabellstørrelsen. Hvis det er snakk om et par hundre tusen innlegg så vil det kanskje være å overdrive dersom en indekserer hele feltet. Det kan bli mye jobb for mysql å lagre. Det muligens også tilfeller det et kan være raskere å indeksere kun første tegnene, men igjen så er det avhenig av forutsettningene. Det blir en priorietring av hva som er mest verdt i forhold til hvor søking det skal gjøres mot opptak av disk plass.

 

Når en er avhengig av denne optimalisering på dette nivået er det også viktig at en leser om tabelltypene. Det er ikke alltid MySQL klarer å gjette bra dersom den bruker B-tree. Andre løsninger er R-tree og HASH (tabeller som lagres i minnet). Tror dette er litt uoffesielt, så kan ikke garantere at tallet stemmer ekstakt, men jeg mener å huske at det gikk en del rykter om at MyISAM ble skrevet for 90% les.

 

Slik som ved avanserte tellere så er det tidsbesparende å pakker tabellene å slå de sammen (merge). En mister da mulgiheten til å skrive mer til de, men de blir mindre og raskere.

 

Et godt råde kan være å benytte unike felter, enten unik indeksering eller primary key. Dette hjelper betraktlig på mysql sitt forsøk på å gjette antallet rader den trenger

 

For de som ønsker å sette seg grundigere inn i dette burde de starte i MySQL manualen. Start med å slå opp tabelltyper og hvordan MySQL bruker indekseringer: http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html. Det neste skrittet kan være å lese om indekseringene: PRIMARY KEY, UNIQUE, INDEX og FULLTEXT - og ta en kikk på lagringsmåtene: B-trees, R-trees og HEAP.

 

Teksten ovenfor er sterkt eksemplifisert og er mer ment som en lett innledning.

[knatten]

Flott innlegg "????????"! Du fikk vel oppsummert mye der, men jeg vil bare nevne en ting til med indexer. De tar ikke bare plass, de tar også tid å bygge. INSERTs tar lenger tid jo flere og større indexer du har. Derfor er det viktig å avpasse hva du bygger indexer på. Men i en blogg er det ikke så mye som skrives, så du kan trygt klæsje på med de indexene du vil egentlig.

[RipZ-]

Besparelsen ved indeksering er veldig forskjellig. Spesielt om man joiner flere tabeller sammen vil indeksering av join-verdiene være veldig gunstig. Her kan man oppleve å gjøre scriptene flere hundre ganger raskere ved å joine et par tabeller med noen tusen rader hver.

 

Som knatten nevner tar det lengre tid ved INSERTs når man har mange indekser. Derfor kan det være lurt på enkelte scripts som sjeldent kjøres (statistikk o.l) og legge på indexene i det scriptet kjøres, for deretter fjerne indexene når scriptet avsluttes.

[????????]

Hva mener du RipZ-?

Å legge til indexer på store databaser tar meget lang tid, så da er det bedre å opprette en index på tabellen før det legges inn data. Eller misoppfattet jeg innlegget ditt?

Endret 18. oktober 2004 av ????????

[Torbjørn]

jeg fikk out of memory error på en fulltext index jeg prøvde å legge til

 

*huske å slenge mere swap neste gang*

[RipZ-]

Nei, du missforstod antageligvis ikke innlegget mitt.

 

Jeg har iallfall lest,at det er raskere å legge inn indeksering for hver gang man utfører visse type spørringer (spørringer med mange joins osv). Det er riktig at det tar lang tid, men om man fokuserer på optimalisering for skriving til databasen hvor man sjeldent leser data, kan det å ikke bruke indeksering være raskere.

 

Nå skulle jeg gjerne kjørt noen tester selv, men siden jeg ikke får tilgang til databasen min akkurat nå, går det dessverre ikke. Uansett, cluet er at det er raskere å legge til indeksering + kjøre spørringen, enn å bare kjøre spørringen uten indeksering (snakk om select-spørring).

 

Kan være jeg begynner å bevege meg på tynn is nå. Men dette er noe jeg mener å ha lest. Jeg har dessverre for lite erfaring med denne teknikken for å si om det fungerer i alle tilfeller på beregnet måte.

 

Edit: for enda en tråd som diskuterer noe litt dypere enn helt ordinær PHP!

Endret 18. oktober 2004 av RipZ-

[Torbjørn]

RiPZ - tror du er på meget tynn is.

 

Den tiden du sparer på å gjøre spørringen på en indeksert tabell, det er den tiden som allerede brukt på forhånd for å indeksere tabellen. og mere der til. tabellen er indeksert for å optimalisere alle spørringer, ikke bare de radene du spør om der og da.

 

jeg kan vanskelig se for meg at dette skal lønne seg utenom helt merkelig bisarre tilfeller.