[totoo]

Det är en grej som jag tycker är mysko. Men det blir väl så när man har lärt sig av vad folk har sagt och inte ifrån manualen...

Jag försöker alltid undvika att ha index som använder sig av fler än ett fält, främst för att jag aldrig har haft stenkoll på hur det påverkar mina sql-frågor vid olika situationer. Men nu vore det skönt att reda ut saker, för enligt explain fungerar det inte som jag har trott att den har gjort.

Låt säga att vi har ett index med två fält (f1 och f2).

Då har jag "fått lära mig" att detta index bara används i en query som använder båda fälten för att ta fram resultatet, alltså t.ex "select * from tabell where f1=1 and f2 > f1".

Men enligt explain på "select * from tabell where f1=1" så används mitt index här, trots att bara det ena fältet finns med i frågan.

Däremot, när jag kör explain på "select * from tabell where f2=1" så används inte mitt index, vilket gjorde mig mycket förvånad.

Om jag använder båda fälten i min fråga så används mitt index också, precis som det borde (oavsett vilken ordning de skrivs i... om det nu kan göra skillnad normalt)

Jag förstår inte varför mitt index inte fungerar om jag använder bara fält2...

Mitt index ser ut såhär

PRIMARY (f1, f2)

Jag förstår inte, och jag är för trött för att läsa manualen och därför hoppas på att få vakna och sedan få äta frukost till ett bra svar här.

Tack.

[WizKid]

Det är precis som telefonkatalogen. Tänk dig att alla bokstäverna i efternamnet är ett fält. Där är alla först sorterade efter första bokstaven i efternamnet och sen andra bokstaven. Så tex går det fort att alla som börjar på A. Det går även fort att hitta alla som börjar på A och har andra bokstaven B. Men däremot om du försöker hitta alla efternamn som har andra bokstaven B så finns det inget snabbt sätt att göra det utom man är tvungen att gå igenom alla namn.

[eg0master]

Databasmotorn kommer använda det index som är "bäst" för att lösa din fråga. Om vi fortsätter på telefonboksexemplet, men modifierar det så det blir relevant och liknar din frågeställning så tänk dig att du har en tabell med kolumnerna EFTERNAMN, FÖRNAMN och NUMMER. Ditt index är på "EFTERNAMN, FÖRNAMN". Detta innbär att indexet är sorterat på i första hand efternamn och i andra hand förnamn. Precis som i telefonkatalogen.

Om du nu skall göra en sökning efter "Andersson, Anders" så är indexet självklart värdefullt. Även när du söker på "Andersson" blir ditt index värefullt eftersom det i första hand är sorterat på eftersnamn.

men om du sitället bara vill söka efter "Anders" så inser du (med telefonboksliknelsen) att indexet är värdelöst eftersom du måste titta igenom hela telefonkatalogen för att hitta alla som heter "Anders" eftersom soreteringen på efternamn inte tillför något.

Lösningen är flera index. Antingen kompletterar du med ett till index för bara FÖRNAMN om vi fortsätter med exemplet eller så har du ett på abar EFTERNAMN och ett på bara FÖRNAMN. Beror ju på om de två kolumnerna i praktiken verkligen är en unik nyckel eller inte.

[WizKid]

Nackdelen med två olika index är att MySQL i alla fall i MyISAM tabeller bara använder ett index per tabell och fråga.

[totoo]

Hmm okej, tack för svaren...

Anledningen till att jag har båda fälten i samma index är för att kunna använda replace. Fälten tillsammans bildar en unik nyckel och replace kan ju användas mycket smidigt då. Det kanske går att använda replace eller liknande metod även om det inte är en primärnyckel, men det är inget jag känner till (alltså replace som kan gå på en kombination mellan två vanliga index).

Äh, men jag nöjer mig nog så länge. Tack för infon gubbar!

ps: jag vet att jag är inkonsekvent med termerna, jag talar så sällan databasprylar med folk så jag har så att säga inte blivit van med något.

[eg0master]

Originally posted by WizKid@May 20 2007, 23:06
Nackdelen med två olika index är att MySQL i alla fall i MyISAM tabeller bara använder ett index per tabell och fråga.

I praktiken är det enligt min erfarenhet inte något problem eftersom ett index per tabell per fråga är tillräckligt. Att använda flera index på samma tabell i samma fråga tillför sällan något. Det skall vara väldigt speciella fall för det. Och därför kan man tjäna mycket på att ha flera olika index beroend epå hur frågorna ställs.

[WizKid]

Att använda ett index med två fält är tex väldigt användbart om man även sorterar resultatet. Ställer man tex frågan "SELECT * FROM users WHERE group_id = 5 ORDER BY username" och har ett indexet innehållande fälten group_id, username kommer resultatet vara sorterat automatiskt och själva sorteringen behövs inte göras.

Men självklart ska man anpassa indexen efter de frågorna man ställer.

[totoo]

mja, jag förstår det... Det finns nog mycket finputs man kan göra med mina tabeller genom att kombinera fält i samma index sådär. Jag har inte hållt på med mysql i så många år, så jag har inte blivit sådär superhardcore som jag hoppas på att vara om typ fem år.

Någon som har tips på böcker som inte är skittråkiga att läsa som behandlar lite sådana "knep"? Jag har redan läst en optimeringsbok, men det är lätt att tappa intresset om den är lite tråkig... Iofs, en liten guide / artikel är att föredra snarare än bok.

[mbomelin]

Alltså, att ha flera index och flera nycklar per index kan vara väldigt nyttigt på rätt ställe.
MySQL använder indexnycklarna från vänster till höger, alltså om du har ett index på id, firstname, lastname så funkar indexet på en query använder bara id, id och firstname eller alla tre.
Om du t.ex. har en mailtabell med ett fält som heter deleted där du flaggar om mailet är raderat eller inte så kan det vara en bra idé att ha ett index på to_user, deleted eftersom du gärna vill SELECT ... FROM mail WHERE to_user=mittuserid AND deleted=0.
Låt säg att du i detta fallet bara har indexerat på to_user och användaren har 20.000 raderade mail så letar mysql iallafall igenom alla rader inkl de 20.000.

Ett exempel där det kan vara nyttigt att ha flera index är t.ex. i en sök-tabell där man kan välja att söka på ett flertal parametrar och utelämna vissa.
T.ex. söka på del av användarnamn, kön, ålder och län eller bara t.ex. söka på kön.
Då kan du omöjligt få ihop ett index som funkar left-to-right utan får göra flera index.

[WizKid]

Det mesta jag har lärt mig är från boken High Performance MySQL (http://highperformancemysql.com/).