SQL indeks vs brute force – sådan redder du dine queries i tide

Som begynder kan du komme langt ved at stille to spørgsmål:

1. Står der type = ALL og mange rows på en stor tabel?
2. Er key tom, selvom jeg filtrerer på en bestemt kolonne?

Før og efter indeks

Hvis der ingen indekser er, udover primære nøgler, vil du ofte se noget ala:

• table = c, type = ALL, rows = 50000
• table = o, type = ref eller ALL afhængigt af setup

Vi tilføjer nu to indekser:

CREATE INDEX idx_customers_email
ON customers(email);

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at);

Kører du EXPLAIN igen, vil du forhåbentlig se:

• table = c, type = ref, key = idx_customers_email, rows = 1 (eller meget få)
• table = o, type = ref eller range, key = idx_orders_customer_created

Selve tallene afhænger af dine data, men pointen er: databasen kigger på langt færre rækker.

Hvilke kolonner giver mening at indeksere?

Nu til det spørgsmål, jeg selv sad med i starten: hvornår skal man lave et indeks, og på hvad?

Kolonner der typisk er gode kandidater

• Kolonner du ofte bruger i WHERE, med =, <, >, BETWEEN.
• Kolonner du joiner på, typisk foreign keys.
• Kolonner du ofte sorterer på med ORDER BY, især sammen med LIMIT.

Eksempler i webshoppen:

• customers.email (unik login-identifikator)
• orders.customer_id (join mellem kunder og ordrer)
• orders.created_at (seneste ordrer, rapporter over tid)
• products.category (filtrering på kategori i produktlister)

Kolonner, du ofte ikke skal indeksere

• Små low-cardinality felter, fx status med 3 mulige værdier, alene.
• Kolonner der sjældent bruges hverken i WHERE, JOIN eller ORDER BY.
• Store tekstfelter (TEXT / LONGTEXT) uden meget klare søgebehov.

Low-cardinality betyder, at der er få forskellige værdier i kolonnen. Et indeks på en status med værdierne 'pending', 'paid', 'cancelled' hjælper ikke meget alene. Databasen ender stadig med at kigge på ret mange rækker per værdi.

Samlede (komposit) indekser

Du kan også lave indekser på flere kolonner:

CREATE INDEX idx_orders_customer_status_created
ON orders(customer_id, status, created_at);

Her er rækkefølgen vigtig. Som tommelfingerregel:

• Sæt de kolonner forrest, som typisk er i WHERE med =.
• Sæt sorterede kolonner (ORDER BY) senere i indekset.

Et indeks på (customer_id, created_at) kan fx bruges til:

WHERE customer_id = ?
ORDER BY created_at DESC

Men ikke effektivt til:

WHERE created_at = ?

JOIN performance: foreign keys og kardinalitet

JOINs kan være billige eller dyre. Forskellen afhænger ofte af indekser og hvor mange rækker, der egentlig matches.

Indeks på begge sider af joinet

Når du har:

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id;

så vil du normalt have:

• Primærnøgle-indeks på customers.id (automatisk)
• Indeks på orders.customer_id (ikke altid automatisk)

Mange ORM’er laver automatisk indeks på foreign keys, men jeg har set projekter, hvor det ikke er sket. Så er det manual arbejde.

CREATE INDEX idx_orders_customer
ON orders(customer_id);

Uden det indeks kan databasen blive nødt til at scanne store dele af orders for at finde matchende kunder.

Kardinalitet: hvor mange forskellige værdier?

Kardinalitet lyder fancy, men handler bare om: hvor mange unikke værdier er der i kolonnen?

• customer_id i orders: høj kardinalitet (mange kunder).
• status i orders: lav kardinalitet (få statuser).

Indekser på kolonner med høj kardinalitet er ofte mere nyttige, fordi databasen kan filtrere meget hårdere.

Typisk begynderfejl med JOINs

• JOIN på tekstfelter (fx email) i stedet for id’er.
• Manglede indeks på foreign keys.
• Filtrering på ikke-indekserede kolonner i den store tabel.

Hvis du vil nørde mere join-performance og SQL generelt, har jeg også lavet en artikel om SQL SELECT-øvelser med mini-dataset, hvor du kan få styr på JOIN-mønstrene først.

LIKE, ORDER BY og pagination: her går det tit galt

Tre ting dukker konstant op i langsomme logfiler: søgning, sortering og pagination.

LIKE-søgninger

LIKE 'abc%' kan ofte bruge et indeks på name.

LIKE '%abc%' kan sjældent bruge det samme indeks effektivt, fordi databasen ikke ved, hvor i strengen den skal starte.

I en lille webshop kan du slippe afsted med det. Men hvis du laver et søgefelt, der bruger % foran og bagved, så overvej:

• Fuldsøgning (FULLTEXT) hvis databasen understøtter det.
• Begrænse funktionen: fx kun prefix-søgning.

ORDER BY + LIMIT

Det her mønster ser uskyldigt ud:

SELECT *
FROM products
WHERE category = 'Shoes'
ORDER BY created_at DESC
LIMIT 20;

Men uden et indeks på (category, created_at) kan databasen være nødt til:

• Slå alle category = 'Shoes' op
• Sortere dem alle
• Så tage de første 20

Med et passende komposit-indeks kan den i stedet hente de 20 nyeste direkte fra indekset, hvilket er markant hurtigere.

OFFSET pagination

Den klassiske:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

Her er problemet, at databasen ofte skal finde de første 10020 rækker for derefter at smide de 10000 væk.

I små projekter lever man ofte med det, men teknikker som “seek pagination” (hvor du paginerer med sidste id/timestamp) kan være værd at kigge på, hvis din side vokser. Det er et emne til en hel artikel for sig selv.

Mini-øvelse: gør 2 queries hurtigere

Nu til den del, hvor du faktisk kan se forskel. Jeg tager to konkrete queries og viser, hvordan du kan tænke.

Opgave 1: Seneste ordrer per kunde

Query:

SELECT o.id, o.created_at, o.status
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.email = 'test@example.com'
ORDER BY o.created_at DESC
LIMIT 20;

Trin 1: Kør EXPLAIN før indeks

Se hvilke tabeller der scannes, og hvor mange rækker den forventer at kigge på. Notér især:

• Bruger den et indeks på customers.email?
• Bruger den noget på orders.customer_id + created_at?

Trin 2: Lav 2 indekser

CREATE INDEX idx_customers_email
ON customers(email);

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at);

Trin 3: Kør EXPLAIN igen

Nu vil du typisk se:

• Få rækker læst fra customers.
• Range-scan på orders for en bestemt customer_id, allerede sorteret efter created_at.

Hvis du har adgang til at måle tidsforbrug (fx SHOW PROFILES, logfiler eller query monitor), kan du ofte se en ret markant forskel.

Opgave 2: Produkter i kategori med sortering

Query:

SELECT id, name, price
FROM products
WHERE category = 'Shoes'
ORDER BY price ASC
LIMIT 50;

Trin 1: EXPLAIN uden indeks

Her ser du typisk:

• type = ALL på products
• Ingen brugt key
• Højt rows-tal hvis tabellen er stor

Trin 2: Lav et komposit-indeks

CREATE INDEX idx_products_category_price
ON products(category, price);

Trin 3: EXPLAIN igen

Nu skulle du gerne se, at den bruger idx_products_category_price, og at antallet af læste rækker går ned, især hvis du har mange kategorier.

Det er den samme øvelse, du kan lave på dine egne queries: EXPLAIN, gæt et relevant indeks, lav det, EXPLAIN igen.

Tjekliste: indeks-regler til små webprojekter

Hvis du bare vil have et lille sæt huskeregler til dit næste projekt, så er det nogenlunde de her, jeg selv følger i små hobbyprojekter og mindre kundeprojekter.

1. Altid indeks på foreign keys

Hver gang du har en _id der peger på en anden tabel, laver jeg næsten refleksagtigt et indeks:

CREATE INDEX idx_orders_customer
ON orders(customer_id);

Det gælder også fx product_id i order_items osv.

2. Indeksér de vigtigste søgefelter

Hvis du ved, at brugere ofte søger efter noget bestemt (email, brugernavn, produkt-id, ordre-id), så skal de kolonner have indekser.

3. Kombinér WHERE og ORDER BY i ét indeks

Har du et typisk mønster som:

WHERE customer_id = ?
ORDER BY created_at DESC

så laver jeg et komposit-indeks med samme rækkefølge:

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at);

Det hjælper ofte mere end to separate indekser.

4. Undgå at indeksere “alt muligt løst”

Hvis du laver et indeks, så gør det fordi en konkret query eller et konkret mønster har brug for det. Ikke bare “for en sikkerheds skyld”.

Jeg plejer at starte uden ekstra indekser, se hvor flaskerne opstår, og så tilføje målrettede indekser på baggrund af EXPLAIN og reelle queries.

5. Brug EXPLAIN som standardværktøj

Når en query føles langsom, er min rutine:

1. Kør EXPLAIN <min query>.
2. Find tabellen med størst rows og type = ALL.
3. Overvej indeks på de kolonner, du filtrerer eller joiner på der.
4. Lav indeks, kør EXPLAIN igen.

Samme tilgang kan du bruge på de øvelser, du finder i fx SQL SELECT-opgaverne for begyndere eller i dine egne små projekter.

6. Tænk en smule frem, men ikke for langt

Du behøver ikke designe en enterprise-database til en lille hobby-blog. Men hvis du forventer, at en tabel kan vokse til hundredetusinder af rækker (fx logins, pageloads, ordrelinjer), så giv de vigtigste queries en chance fra start med få, men velvalgte indekser.

Hvis du vil bygge videre på SQL-delen, kan du også kigge på mere strukturerede øvelser, fx lave dine egne queries på et test-dataset og så måle forskellen med og uden indeks. Det er noget nær den mest håndgribelige måde at lære databasen at kende på.