[mysql] Indexen, waar zet je ze

[megamuch]

Ik ben voor een project bezig een aantal queries te optimalizeren en in de meeste gevallen lukt dat redelijk. Het zijn kleine datasets ( < 1000 records) maar liever een goed datamodel dan een slechte.

Nu loop ik echter tegen wat kleine dingen aan die ik niet goed weet te plaatsen.

Voorbeeldje

SQL:

SELECT 
        discounts.clubid, 
        clubs.naam, 
        clubs.type as tiepe
    FROM 
        discounts
    LEFT JOIN 
        clubs 
            ON ( discounts.clubid = clubs.clubid )
        ORDER BY 
            tiepe, clubs.naam

Levert via explain het volgende op

code:

 id      select_type     table       type    possible_keys       key     key_len     ref     rows    Extra
1   SIMPLE  discounts   ALL     NULL    NULL    NULL    NULL    46  Using temporary; Using filesort
1   SIMPLE  clubs   eq_ref  clubid  clubid  4   bcntest.discounts.clubid    1

Tabel discounts heeft een index unique op clubid

tabel Clubs heeft indexen oa op clubid(unique) en type(index).

Hoe krijg ik nu die Using temporary; Using filesort weg ?

[whoami]

Een index kan je best zetten op velden waar je veelvuldig op zoekt / joined / sorteert.

Weet wel dat indexen ook overhead met zich meebrengen bij updates/inserts.

Ieder DBMS heeft ook zo z'n specifieke eigenschappen. Ik meen ooit eens gehoord te hebben dat MySQL bv per query slechts 1 index / tabel gebruikt. Echter, of dat nu ook nog zo is, weet ik niet.

In jouw geval kan je dus een index leggen op clubid (in de discounts en clubs tables), en op clubs.naam.
Je moet er ook rekening mee houden dat een DBMS enkel een index zal gebruiken als er voldoende unieke waardes zitten in die index, en, als er voldoende rijen in je tabel zetten.
Stel bv een tabel met 10 records, en een index op naam. Je wil die tabel doorzoeken met een filter op naam. Dan kan het goed zijn dat het DBMS die index niet gaat gebruiken, omdat een table-scan in dat geval gewoon sneller zal zijn dan het gebruik van een index.

Ga ook na of je in sommige gevallen best samengestelde of enkelvoudige indexen kunt gebruiken, etc..

[megamuch]

slightly offtopic, een samengestelde index is toch altijd binnen dezelfde tabel en nooit op velden in verschillende tabellen?

[.oisyn]

Er staat ook een heel mooi stuk in de P&W FAQ over indices.

[Upsal]

Beetje offtopic:

Waarom rename je met "clubs.type as tiepe"? Als het gaat om syntax fouten, dan mag je "type" gewoon laten staan, maar evt. wel mysql haakjes erom heen, voorbeeld: "ORDER BY `type`, clubs.naam".

[ Voor 34% gewijzigd door Upsal op 01-06-2006 19:43 ]

[megamuch]

Upsal schreef op donderdag 01 juni 2006 @ 18:22:
Beetje offtopic:

Waarom rename je met "clubs.type as tiepe"? Als het gaat om syntax fouten, dan mag je het laten "type" gewoon laten staan, maar evt. wel mysql haakjes erom heen, voorbeeld: "ORDER BY `type`, clubs.naam".

Nou vooral even voor het testen. backticks zijn irritanter om te schrijven dan tiepe maar in de live code is dat aangepast. (ie met backticks).

@ .Oisyn,

Ik heb de PW faq al meerdere keren geraadpleegd, met name voor joins (het blijft af en toe best lastig), maar de pw faw qua indices is zeker niet heilig. Ik hoop dat de wiki er eindelijk komt bij upgrade naar de volgende react versie. (Ik vond er overigens wel het antwoord op m'n 2e vraag )

Genoeg offtopic,

het hele probleem (van het voorbeeld iig) is opgelost. Door wat andere aanpassingen te maken aan het datamodel, was de hele discount tabel niet meer nodig. (Er is immers slechts 1 discount per club dus die hele tabel sloeg nergens op )

[whoami]

megamuch schreef op donderdag 01 juni 2006 @ 18:06:
slightly offtopic, een samengestelde index is toch altijd binnen dezelfde tabel en nooit op velden in verschillende tabellen?

Jazeker, je kan niet zomaar één index maken die velden bevat die uit meerdere tabellen komen.

(Je kan in Sql Server natuurlijk wel een indexed view maken, waarbij die view 2 tabellen joined, en ... maarja.. )