Indexes og forespørgsler

Primitive tommelfinger regler:
- der er automatisk index paa PK saa undlad selv at saette et index paa
- saet index paa felter der bruges i join
- saet index paa felter der bruge i where
- undgaa like med noget der starter med % fordi det kan ikke bruge index
- index goer queries hurtigere og insert langsommere, men det er som oftest bedre at have for mange index end for faa index da queries bliver meget hurtigere og insert kun lidt langsommere og de fleste har flere queries end inserts

Naeste trin er at identificere de queries som koeres mest og analysere dem.

Analysen bestaar af:
- kigge paa explain output
- maale paa forskellige varianter af query paa en test server

Og ja - det er en masse arbejde!

Jeg har fx et meget konkret spørgsmål:

Jeg har en tabel forum_posts, hvor en af kolonnerne hedder dato.

Altså: forum_posts med col1, col2, col2, dato, col4 osv

Jeg ønsker at få de 10 seneste posts, MEN hvis jeg siger:
SELECT * FROM forum_posts ORDER BY dato DESC LIMIT 0,10
så bliver hele tabellen læst igennem og de 10 øverste rækker bliver returneret.

Kan jeg ikke blot få mysql til at udtrække de 10 øverste rækker i stedet for samtlige?

Ligeså vil jeg gerne kunne sige LIMIT 30, 40 (hvis man har klikket på side 4 i forummet)

Hej Arne,

Tak for din respons.

Jeg kan fx lave index på:
dato
postid
postid, dato
dato, postid

Hvornår er de to nederste med to kolonner i hver index hensigtsmæssig?

Er hovedreglen at man blot skal lave indexes på hver kolonne for sig?

re #2)

Hvis der er index paa data paa burde der ikke laves en full table scan

re #3)

jeg gaetter paa at postid er PK og i saa fald ville jeg starte med kun at lave index paa dato

jeg vil mene at index paa flere kolonner er en sjaelden fugl for index til at forbedre performance (til index som skal sikre unik kombination er det noget andet)

Jeg har fx denne select:

SELECT distinct(id) AS thread_id, title
    FROM forum_thread
    LEFT JOIN forum_visit ON forum_thread.id = forum_visit.thread_id AND forum_visit.brugerid = 7
    WHERE forum_thread.main_id = 3 ORDER BY forum_thread.last_post_datetime DESC LIMIT 0, 10

Den skal give mig de sidste 10 tråde i forum nr. 3

Jeg har prøvet en masse forskellige indexes, og en explain bliver ved med at vise:
forum_visit: rows 1
forum_thread: rows 716

Jeg tvivler paa at det kan goeres bedre end index paa:

forum_thread.id
forum_visit.thread_id
forum_visit.brugerid
forum_thread.main_id
forum_thread.last_post_datetime

Jeg vil lige give et mere enkelt eksempel, som dermed så også trækker problemet ret skapt op:

SELECT brugernavn FROM bruger ORDER BY last_visit DESC LIMIT 500

Jeg har index på last_visit og der vælges kun 500 rækker. GODT!

SELECT brugernavn FROM bruger WHERE myCol = "Y" ORDER BY last_visit DESC LIMIT 500

Nu får jeg langt flere end 500 rækker ... hvordan skal jeg lave denne query/tilhørende index, så jeg kun får de 500 brugere, der sidst har logget ind og som har myCol = "Y" ?

Det som goer den grim er at der formentligt er mange raekker med myCol = 'Y'.

Det foerste du proever er at putte index paa myCol.

Hvis det ikke hjaelper saa proever du med et index paa (myCol,last_visit).

Hvis det heller ikke hjaelper saa skal du have fat paa nogen som ved mere end mig.

Eller dvs. jeg får selvfølgelig ikke flere end 500 rækker ud, men min joinsize ved explain giver et meget højere tal end 500.

Jeg har både index:

last_visit
myCol
myCol, last_visit

stadig rigtig stort udtræk :-(

Tak for forsøget, jeg håber, der er andre der melder sig på banen her?

Jeg vil ogsaa foreslaa at du maaler performance fremfor udelukkende at kigge paa explain.

Det er en helt ny bane for mig, hvordan gør jeg dette lettest?

Jeg er klar over, at man kan udtrække 2 forskellige forespørgsler evt. 100 gange i træk og tage gennemsnittet. Er det mådén at gøre det på?

Ja.

Bortset fra at du muligvis boer koere flere i parallel for at faa et realistisk billede.

Dvs. flere kørsler på én gang?

Hvorledes gør man dette?

Der er tools som kan bruges.

Det simple er en loop i en bat fil som kalder wget.

Hej Arne,
Du har ret, nu har jeg blot indekseret på de enkelte kolonner, der benyttes i where, og det er lige så godt som mine lange lange index(a,b,c). Det har garanteret sparet serveren for en masse plads, uden at jeg ved hvor meget?

Mit egentlige problem var vist ikke selve indekseringen, som jeg troede for længe siden (derfor rettede jeg fra enkelt-indekses til de sammensatte i sin tid) men faktisk sammensætningen af forespørgslerne, som blot ikke benyttede dem korrekt.

Der er jeg da kommet en del længere nu :)

Jeg har muligvis fundet, at der dog er en fordel med index(a,b,c) hvis man i explain får vist "func" i kolonnen ref. Kan det passe?

Maaske - saa godt kender jeg ikke MySQL optimizeren.

Tid at faa afsluttet her?

??

Vil du lægge et svar?

kommer her