Mest optimale WHERE criterier

Har du prøvet at teste performance ved at fylde et masse dummy data i tabellen og se hvor hutigt det går ?

Nej, jeg havde håbet på at der var et lidt mere begrundet svar end at afprøve det i praksis;)

Indexes kan ikke benyttes til at kigge på en kolonne i indexet, med mindre alle foregående kolonner også benyttes - det er fordi et index reelt er en liste der er sorteret ud fra den rækkefølge der angives af feltlisten.

Du bør faktisk lave et nyt index med Felt3 for at få optimal performance på det punkt (alternativt ændre IDX2 til F3,F2,F4 hvis du aldrig søger på Felt2 uden Felt3, eller bare smide F3 på enden af IDX1, hvis det er muligt).

Der kan selvfølgelig være ANDRE ting du gør galt i din query, men hvis det er en simpel SELECT FROM WHERE og du ikke SELECT'er *, så skulle din query være god nok.

At lave nye index er ikke en mulighed, og jeg er egentlig mest interesseret i en generel besvarelse af spørgsmålet.

Jeg har selv følgende overvejelser, som i måske kan svare på om er korrekte :
Benyttes den simple WHERE vil jeg antage at ID1 benyttes. Herefter skal samtlige rækker som matcher, chekkes for kriteriet på Felt3.

Tilføjes Felt2 og Felt4 til WHERE clausen, så må begge index kunne benyttes. Benyttes IDX1 så vil der være flere kriterier Felt2,Felt3 og Felt4 som rækkerne skal vurderes udfra. Benyttes IDX2 derimod skal der kun benyttes et kriterie, Felt1 til at vurdere om de fundne rækker matcher.

Hvis ovenstående er korrekt vil det vel være bedst kun at benytte Felt1 og Felt3 i WHERE clause'n, såfremt IDX1 er mere selektivt en IDX2, da det vil give færre rækker som skal evalueres mod Felt3 kriteriet? Omvendt, hvis IDX2 er mest selektiv, så vil det vel være mest optimalt at give optimizeren mulighed for at vælge dette index?

Felt4 er irrelevant for hvorvidt indexet kan benyttes - MSSQL kan sagtens finde ud af at se bort fra kolonner der kommer EFTER dem du skal bruge (problemet er kun hvis de kommer FØR, fordi et index reelt er en sorteret liste). Det svarer det til at det er relativt let at slå op i et leksikon for at finde alle de emner der starter med 'E', mens det er meget besværligt at finde alle de emner der har et 'O' som det andet bogstav.

Hvis du får Felt2 flettet ind i din query på en måde der rent faktisk giver anledning til at bruge indexet, kan MSSQL benytte BEGGE på en gang, hvilket jo netop er det optimale (og grunden til at du med den query burde lave et nyt/ændre et eksisterende) - men det er nu noget lettere sagt end gjort, hvis resultatet skal blive det samme.

"Benyttes den simple WHERE vil jeg antage at ID1 benyttes. Herefter skal samtlige rækker som matcher, chekkes for kriteriet på Felt3."  Det er givetvis korrekt. Er antallet af rækker der matcher felt1 beskedent gør det ikke meget at der skal et linieært gennemløb af dem til for at finde dem hvor felt3 matcher.

pidgeot>> Er det virkelig rigtigt at begge index kan benyttes til evaluering af samme statement ved 1 afvikling ?

Det "problem" jeg så ved at benytte den "store" WHERE er hvis IDX1 vælges af optimizeren. Jeg vil da tro, at samtlige rækker skal vurderes ud fra om kriterier på felt2 OG felt 3 passer, hvilket vil være et check mere end hvis jeg IKKE giver de ekstra kriterier med i clausen.

Ja, den kan sagtens benytte begge - hvis den vurderer det bedre kan betale sig at scanne de matchende records igennem direkte i tabellen, så gør den det, men der er jo intet i vejen for at den bruger det andet indeks på de referencer den har fra det første index.

Da et multi-column index essentielt gemmer værdierne som om de sad umiddelbart i forlængelse af hinanden (der er så vidt jeg husker lidt mere i det end det, men lad os holde det simpelt her), behøver det i princippet ikke kræve mere tid end hvis det kun var et felt, omend det forudsætter at det kun er den sidste kolonne der ikke skal have én fast værdi.

Overvej evt. at læse hvad MySQL skriver om indexes - ganske vist er der utvivlsomt forskelle, men lur mig om ikke de fleste af principperne også gælder for MSSQL :)

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Måske sagt på en lidt bedre måde: Selv om den ikke rent faktisk bruger begge i forlængelse af hinanden (det mener jeg at have hørt at MSSQL godt kan), så vil alene det at den har MULIGHEDEN være en fordel, da den så selv kan vælge det mest effektive index (hvilket den jo alligevel ville gøre for at få så få records som muligt at skulle checke op mod i det andet indeks).

pidgeot lægger du et svar så vi kan få lukket her?

Gerne :)

beklager jeg troede jeg forlængst havde fået lukket her