Index og hastighed

Du skulle prøve at tage et lille kig på http://www.mysql.com/doc/en/EXPLAIN.html. Ved hjælp af explain (din sql-sætning), kan du se, hvilke indexes der bliver anvendt. Samtidig bliver brugen af ANALYZE forklaret.

For at et index fungerer, skal databasen have oprettet nogle søgetræer over indexerne. Hvis du i en tabel med 750.000 tupler opretter et index, giver det intet mærkbart til at starte med. Det skyldes, at der i søgetræet over indexet ikke er oprettet, det oprettes nemlig idet du anvender indexet. Hvis du laver en tom tabel med de rigtige indexer og flytter hver enkel tupel fra din store tabel over i den nye tabel, vil søgetræet blive oprettet.

Brugen af ANALYZE holder ligeså søgetræet vedlige, og bør dermed blive kørt jævnligt for at holde hastigheden i top.

I den sql-sætning du viser, bør du som minimum indexere 'node_id' og 'datasource_id' og sikkert også 'time'. Opretter du for mange indexer der ikke rigtig bliver brugt til noget, koster de kun i den forstand, at databasen holder dem vedlige.

Hvis du ofte søger på 2 nøgler adgangen (f.eks. node_id og datasource_id), kan det være en god ide at oprette et unikt index over disse nøgler, hvis altså der findes to ens sammensætninger af dem. Husk desuden at anvende heltal som primærnøgler - gerne autonummerering, da alt andet sløver voldsomt.

Håber det bidrager til noget nyt, du ikke allerede har set på.

Jeg har brugt timer på at kigge på hvad explain fortalte og har forsøgt at indrette mig efter det, dog uden videre held.

Jeg har tidligere forsøgt med index på det du foreslår, men har ikke kunne få det til at fungere. Nedenfor er resultaterne med de 2 forskellige index.
Tabellen 'perfdata' er af MyISAM typen med 734.499 rækker kørende på en MySQL 3.23.55.
Tabellen har fået en OPTIMIZE imellem hver ændring af index og query.


Ifølge MySQL dokumentationen for EXPLAIN, så skyldes "Using temporary" typisk at der sorteres på andre felter end der gruperes, hvilket jo ikke er tilfældet her. Hvad det ellers kan skyldes siger den ikke noget om.
Ligeledes siger dokumentationen at 'possible_keys' er de index som MySQL kan bruge til at finde de relevante data. Den er NULL for begge EXPLAIN, men
alligevel beslutter den sig for at benytte et index i nederste eksempel. Sjovt nok tager en query i selvsamme eksempel længere tid...

Index eksempler kan ses her: http://www.msd-sommerhuse.dk/MySQL.txt

Du kan evt. være nødt til at bruge sum-tabeller til at optimere dine on-line forespørgsler. Måske kan det bare ikke blive hurtigere med den hardware du kører på?

Det giver dig ingenting at anvende index, af den simple grund at du altid skal ALLE dine rækker igennem for at kalkulere max(time) - du anvender mao en Full-table-scan, det kan index ikke rette op på.

e.oersted >> jeg kan godt huske en logisk forklaring på at visse kombinationer af betingelser resulterer i tvungne full-table scans.
Men hvorfor kan den ikke bruge indexes til max()? Den må da kunne starte med at scanne indexet fra den høje ende?
(Det er bare nysgerrighed så du behøver ikke svare hvis du ikke gider ;-) )

Jeg skal ikke gøre mig til ekspert i mySQL's optimizer, men jeg vil tro at alene udeladelsen af en WHERE-clause får den til at vælge full-table-scan.

En anden mulighed kunne være at normalisere din tabel:

Node_Info
  uid - primary key
  node_id,
  datasource_id
  max_time

Node_time
  ni_uid
  time
Hvis du yderligere "denormaliserer" time og indsætter en kolonne max_time i node_info, så skulle du være godt på vej. Det kræver selvfølgelig en ekstra update at checke om nyeste insert af time er større end max_time og opdatere hvis det er tilfældet. Omvendt, hvis du har 750.000 rækker og kun 9 forskellige kombinationer af node_id og datasource_id, så vil denne øvelse også spare dig en del disk-plads.

Har fundet en anden løsning end index, laver en temp istedet og opgivet "live" opdatering.

Eller rettere en nødløsning...