Jeg har en MySQL-tabell som jeg bruker til å loggføre endringer som skjer i noen koordinatsystemer. Jeg har en håndfull kolonner, blant annet 3 for koordinater (en integer for hver dimensjon). Jeg ender opp med ganske mange rader i denne tabellen, og nå begynner det å bli aktuelt å optimalisere den. Her er jeg på litt ukjent farvann, og jeg ønsker litt feedback på tabellstrukturen min.

Ut i fra det jeg har lest om MySQL, så fungerer indekser ved at databasen på ett eller annet vis lagrer informasjon om tabellen som den bruker for å raskere kunne utelukke rader fra spørringer, sånn at den slipper å bla gjennom hele greia fra A til Å hver gang jeg leter etter noe.

Det er i all hovedsak to spørringer som kjøres mot denne tabellen. Det er mange inserts, og av og til en select. Insert blir vel forhåpentligvis ikke påvirket av tabellens størrelse. Når jeg selecter, så har jeg navnet på koordinatsystemet dette gjelder for (en varchar), og de tre tallene for koordinatet.

Er det en god idé å lage indekser for alle disse fire kolonnene, eller vil det bare redusere ytelsen å ha så "mange"? Hvilke situasjoner er det som vil føre til at indekser gjør spørringene tregere? Hvordan påvirker dette databaseserveren ellers, i form av lagringsplass, for eksempel?

Jeg jobber selv med et system der vi har et par hundre tusen nye rader om dagen, og min erfaring er at man tjener mye på å lage grupper av indekser. Overdreven bruk av indekser vil gjøre innsettingen tregere, så du bør kun sette de nøklene du absolutt trenger.

Jeg har en testdatabase med 2.000.000 rader. En spørring som involverte 3-4 kolonner tok i utgangspunktet ~5 sekunder å kjøre igjennom. Etter at jeg reindekserte dataen, og opprettet èn index for de aktuelle kolonnene, fikk jeg den ned til 0,6 sekunder.

Jeg anbefaler deg også å sette opp en testbase med 1-2 millioner rader der du kan prøve ut forskjellige index-nøkler.

Har du mulighet til å poste tabellstruktur og spørringer du bruker? Da er det enklere å hjelpe.

Foreløpig, så er faktisk ikke ytelsen så aller verst, men jeg har ikke nådd så veldig mange rader ennå. Jeg vil bare sørge for at systemet er så bra som det kan bli, sånn at jeg er klar for det som måtte komme senere. Tabellen ser slik ut:

id (int 20)

time (int 20)
user (varchar 20)
action (varchar 50)
typeID (int 5)

name (varchar 20)
x (int 10)
y (int 10)
z (int 10)

Det første feltet her, id, er auto_incrementen, time er en timestamp (UNIX-timestamp fra maskina som kjører spørringene) og resten er bare litt data om hva som foregår. De fire siste er dem jeg bruker for select-spørringer. Slik ser det ut når jeg holder på (dette er prepared statements, derfor ???):

insert into log (time, user, action, typeID, name, x, y, z) values (?, ?, ?, ?, ?, ?, ?, ?)

select time, user, action, typeID from log where name = ? and x = ? and y = ? and z = ?

Det kan også etter hvert bli aktuelt for meg å selecte med range, altså sånn som x > ?, men jeg vet ikke om det vil ha noe å si for ytelse og indekser.