Någon som har ett bra tips om hur man effektivast söker i en tabell med många rader.

Jag har en tabell med t.ex. kolumner för; kön, födelsedatum, bostadsort och civilstatus.

Så vill jag att mina användare skall kunna välja sökbegrepp fritt. T.ex;
- Alla singeltjejer födda mellan 1970-1974
- Alla som är födda 1988 och bor i Göteborg
- etc etc

Mitt problem idag är att det blir seeega sökningar, eftersom jag inte kan(?!) ha index som täcker alla kombinationer.
Nu har jag det för de vanligaste sökningarna men inte för alla. Eftersom det är många rader blir sökningarna kraftödande...

Någon som har någon smart lösning på problematiken??

En sak som kan vara värt att testa är att göra en sökning för varje sökvillkor och sen slå ihop dem. Börja med den som ger minst träffar.

Alla födda mellan 1970 - 1974. Du får en array av ID.
Alla singlar som finns i arrayn. ( SELECT ID FROM members WHERE status = "singel" AND ID IN(alla id från första sökningen) ). Du får en ny array av ID.
Alla tjejer som finns i arrayn.

Eftersom varje sökning nu kan använda index om du sätter ett index på varje kolumn kan det gå fortare. Jag har använt det för en liknande sak och det gick betydligt mycket fortare.

det går att bygga upp en sqlfråga beroende på vad användaren har valt att söka på.

sen alltid när det gäller databaser så är INDEXERING skit viktigt.
det blir sån jääkla skillnad när du byggt ett bra index.

bygg ditt index beroende på vad du har i where:satsen.


lite svårt att säga hur du skall bygga sqlfrågan när man inte vet hur strukturen ser ut :)

men som sagt.. index =)

fredlund: Läste du ens Conth post? Han skriver:
"Mitt problem idag är att det blir seeega sökningar, eftersom jag inte kan(?!) ha index som täcker alla kombinationer.
Nu har jag det för de vanligaste sökningarna men inte för alla. Eftersom det är många rader blir sökningarna kraftödande..."

oj, läste en aning fel.. hehe sorry

Du skulle kunna bryta ut vanliga kombinationer till en egen tabell. Självklart blir det data som du ändå kunde lagrat i samma tabell som personinformationen, dvs risken blir att det blir redundant. Men är det data som uppdateras sällan är det bättre för sökprestanda att lägga det i egna tabeller eftersom sökning kanske sker oftare än uppdatering. Är det tvärtom, dvs uppdatering sker oftare än sökning bör du försöka slå ihop tabeller.

Men exempelvis skulle du kunna ha en tabell som lagrar id:n för vilka som är singlar. Då kan du köra en EXISTS.

Jag är ingen db-guru, men jag skulle iaf fundera i de banorna istället för att ha en bool i en tabell för singel eller ej.

Själv är jag en superfan av kopplingstabeller. (Och sen är EXISTS ett jättebra nyckelord istället för WHERE kolumn IN (...))

Tack för förslagen, jag får prova mig fram lite.

Jag har byggt en strippad söktabell idag med de vanliga sökvärdena i, problemet är ju att det är relativt mycket uppdateringar och det gör att jag får tyngre för varje nytt index jag lägger på...

Varför kan du inte sätta index på alla kolumner?

I mina öron låter det som att du har ett normaliseringsproblem.. :)
dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Tala gärna om vad som fungerar bäst ifall du hittar någon bra lösning, kommer själv råka ut för samma sak framöver...

Jag förutsätter att du använder en av de vanligare SQL-databserna.

Det låter som det bara handlar om svenska personer. Eftersom det bara bor 10 miljoner pers i Sverige kan det inte vara problem med de där sökningarna. Släng på ett standardindex på alla kolumnerna, läs manualen till din databasmotor för index och se om du inte kan slänga in mer specifika index (booleska tänker jag på i första hand) när du tagit reda på vad de är vanligt att söka på. Så länge tabellen inte uppdateras mycket kan du köra massor av index men förmodligen räcker det med få väl utvalda istället.

Min erfarenhet är att väldigt många som "jobbar med web" envisas med att använda strängar stup i kvarten i sina databaser för saker som inte bör vara strängar. Dvs att man har en "varchar(4)" för födelseår till exempel (eller varchar(8) för födelsedatum). Sådant kan avsevärt försämra effektiviteten av index då sökningar på tal (inkl datum) oftast är betydligt mer effektiva.

Har du till exempel "Göteborg" som en text i din tabell eller är hemorten bara ett ID som refererar till ortstabellen? Det är ett annat typiskt "webfenomen" (dvs att inte normalisera i tillräcklig utsträckning).

Annars är det nog som någon föreslog att det bästa är att istället för flera olika index med flera kollumner enbart ha ett index per kollumn. Databasmotorn kommer sannolikt välja ett av indexen som ger minsta submängd att jobba vidare med (dvs har du bara två från göteborg väljs dessa ut först om du har 100 födda 1988).

Jo det är mysql 5

Som jag sa tidigare så uppdateras tabellen tämligen ofta, vilket gör att jag inte vill slänga på för många index.

Dessutom funkar det inget vidare med många index...
Säg att jag vill kunna söka på en kombination av 5 olika termer. t.ex. A-E
Jag kan ju inte ha ett index för A+B+C, ett annat för A+D+E, ett tredje för B+C+E etc etc. Om man sedan dessutom vill kunna sortera resultatet (blanda sortering ASC, DESC) måste det till en tablesort även om jag har rätt index.

Det enda jag kan göra(?) är det jag gjort nu tagit de vanligaste sökbegreppen och lag index på dom, men det ger fortfarande en hel del sökningar som hamnar i slow-loggen...

Bra tips, ska dubbelkolla att jag inte missat någon här.

Att använda DATE och YEAR osv är väl en självklarhet. Jag har haft ganska bra erfarenhet av ENUM (som lagras binärt) även om jag egentligen har några säkra uppgifter för effektivitet i sökningar. Det lär ju i alla fall löna sig att välja sina lagringstyper med omsorg.

Jag skulle önska att det var så, men tyvärr hittar jag varchar allt för ofta i system gjorda av "webprogrammerare"...

Fler tips:

- Byt ut varchar(NUMMER) mot char(NUMMER). Då tar alla rader lika mycket plats, vilket gör att databasen inte blir så fragmenterad.
- Använd OPTIMIZE TABLE regelbundet

Att använda char istf varchar blir man ju vansinnig av. Det blir ett satans trimmande hela tiden. Jag skulle starkt avråpde från att använda char för annat än väldigt korta och garanterat exaklt lika långa värden (t.ex. bokstavsförkortningar för stater i USA eller Län i sverige).

I alla de system jag någonsin varit med och utveckla (och då vill jag påminna om att det inte är web som är min huvudsyssla utan andra typer av system) har jag aldrig själv eller sett någon annan använda char istf varchar.

Vad menar du med "trimmande"?

Saxat från http://dev.mysql.com/doc/refman/5.0/en/data-size.html:

Med trimmande menar jag att om du använder char för något som egentligen är av variabel längd lär du vilja trimma bort space för att kunna göra vettiga jämförelser/utskrifter. Jämför funktionen "trim" som förekommer i de flest språk (i anslutning till strängar).

Och vad är det du saxat egentligen? Vad vill du säga med det? Ärligt talat tycker jag det är ofta du Blackex kommer med inlägg som känns gripna ur luften och inte alltid helt relevanta. Ibland (som i detta fall) känns det mer som ett reultat av en googling än ett relevant svar. En orsak skulle kunna vara att du (liksom jag gör ibland) tänker i flera steg och att dina inlägg saknar några associationssteg och därför blir obegripliga.

Trimmningar behöver ej göras. Att välja CHAR istället för VARCHAR har endast betydelse "bakom kulisserna", dvs i databasen. När du hanterar datan så blir det alltså inte en massa onödigt tomrum. Ur användningshänseende är alltså CHAR och VARCHAR exakt lika. För att vara extra tydlig, du behöver alltså inte använda trim() eller motsvarande funktion för att tömma strängen på extra fluff :)

Du har förmodligen rätt :) Jag trodde att jag var tydlig. Jag saxade från MySQL manualen. MySQL, antar jag, är den databas som de flesta här använder. Sidan som jag saxade ifrån handlar om hur man får bättre prestanda genom att använda olika tekniker.

Dokumentationen bekräftar alltså det som jag säger. Allt kolumner med fast storlek går snabbare att accessa. Använder du VARCHAR tar det mindre utrymme, men går långsammare. Därav mitt ursprungliga tips om att använda CHAR och inte VARCHAR.

Förstår du relevansen nu? :)

Kul att man får lära sig något nytt. MySQL är helt klart magisk. Vad praktiskt (om man nu inte mot förmodan vill behålla "traling space" i databasen). Ja då minskar definitivt anledningen till att använda varchar för mysql.
Har inte testat MSSQL, för jag är definitivt säker på att det åtminstopne i tidigare versioner av någon databas jag jobbat med (Men jag kan i nuläget inte minnsa om det varit Access, MSSQL eller Sybase) som inte strippat space från char på det sätt MySQL gör.
Eftersom diskplats är billigt så är definitivt char ett lämpligt alternativ till varchar för att förbätgtra prestanda.
Absolut.

Kanske värt att understryka:if you do not have any variable-length columns, alltså en riktig effektivitetsvinst får man antagligen bara om man håller hela tabellen med fasta storlekar (DATE, INT, CHAR etc) och helt undviker t ex TEXT och VARCHAR i den tabellen.

Jag håller med eg0master om att VARCHAR kan vara mer praktiskt ur platsbesparingssynpunkt, men frågan här var ju egentligen hur man kan få snabbare sökningar. Och det ger CHAR. Tycker kanske det var lite överdrivet att gnälla på Blackex, även om inlägget var saxat från doc:en så var det relevant.

Vad det gäller ett begränsad antal strängar som inte förändras ofta skulle jag föreslå ENUM istället för (VAR)CHAR, vilket borde ge betydligt snabbare sökningar i större tabeller.

Nackdelen med char(NUMMER) mot varchar är att det tar *massor* av extrautrymme. Utrymme som man kan använda till att cacha data/index.

Och ett tips dom många missar är att lagra IP-nummer som "dotted quad" (222.222.222.222) istället för det 32-bitars nummer det egentligen är.

Edit : *suck* Jag SKA läsa färdigt tråden INNAN jag kommenterar ;)