[MySQL] Database optimalisatie

[Rekcor]

Na een uur op verschilende forums rondgezworven hebben, toch maar een vraag hier op T.net:

Ik heb een erg simpele databasetabel:

Tabel 'Producten'

code:

ID                   int(100)     
code                 varchar(20)
plantId              int(200)   
verschijningsvormId  int(11)    
kleurcodeId          int(11)    
locatieId          int(11)  
standaardprijs       float  
voorraad            int(11)     
dh_timestamp        int(11)     
dh_userName          varchar(100)

Met de volgende indexes (nodig omdat locatieId t/m plantId foreign keys zijn van lookup tabellen)

code:

PRIMARY              PRIMARY  ID
locatieId          INDEX
verschijningsvormId  INDEX
kleurcodeId          INDEX
plantId              INDEX

Gewoon een recht-toe-rechtaan tabel lijkt me: je hebt een product met een uniek ID. Ieder product heeft een code, een plant, een kleurcode, een locatie, een standaardprijs, een voorraad, een timestamp en een userName (van degene die het product heeft ingevoerd in de db)

Als ik echter EXPLAIN SELECT * from producten doe, krijg ik:

code:

id             1
select_type    SIMPLE
table          Producten
type           ALL
possible_keys  NULL
key            NULL
key_len        NULL
ref            NULL
rows           267

type=all dus, en possible_keys=null. Nogmaals, ik heb al flink lopen zoeken op het internet, maar kan echt niet vinden waar dit aanligt. Als je mijn tabel ziet, zou possible_keys toch gewoon ID, en type gewoon index moeten zijn?

[GlowMouse]

Indices hebben pas zin als je een beperkte selectie maakt, sorteert, of tabellen koppelt. Wanneer je gewoon alle rijen terug wilt hebben, valt er niets te versnellen.

[Rekcor]

GlowMouse schreef op woensdag 31 januari 2007 @ 12:10:
Indices hebben pas zin als je een beperkte selectie maakt, sorteert, of tabellen koppelt. Wanneer je gewoon alle rijen terug wilt hebben, valt er niets te versnellen.

Weet ik, maar ik vind het gek dat hij al bij de meest simpele query 'type = ALL' en 'possible_keys = NULL' geeft...

[lier]

Wat verwacht je dan ?

[bigbeng]

EXPLAIN levert je het execution plan op van je query. Ik neem aan dat je dat begrijpt.

Stel je nu eens voor dat jij de manager bent van een kaartenbak met personen. Vooraan in de kaartenbak staat een index die je vertelt hoe je op achternaam een persoon kunt vinden. Nu komt er iemand naar je toe en die zegt doe mij alle kaarten uit de kaartenbak. Ga jij dan nog de index raadplegen?

[Rekcor]

Ik begrijp het. Eigenlijk is mijn query veel ingewikkelder dan SELECT *:

code:

SELECT producten.ID, producten.code, CONCAT(planten1.voornaam, " ", planten1.achternaam) AS 'Plant', verschijningsvormen2.naam_nl AS 'Verschijningsvorm', kleurcodes3.naam_nl AS 'Kleurcode', locaties4.naam AS 'Locatie', producten.standaardprijs, producten.voorraad, producten.dh_timestamp, producten.dh_userName
FROM producten
LEFT JOIN locaties AS locaties4 ON (producten.locatieId = locaties4.ID) LEFT JOIN kleurcodes AS kleurcodes3 ON (producten.kleurcodeId = kleurcodes3.ID) LEFT JOIN verschijningsvormen AS verschijningsvormen2 ON (producten.verschijningsvormId = verschijningsvormen2.ID) LEFT JOIN planten AS planten1 ON (producten.plantId = planten1.ID)

en vroeg ik me af waarom die query niet optimaal was. Nu snap ik dat iedere query, die alle resultaten uit een tabel trekt, automatisch 'type=ALL' heeft voor de primary key.

Ik had in meerdere tutorials op internet gelezen dat dat echt heel slecht is, maar dat ging telkens over queries met WHERE restricties.

Goed, bedankt allemaal, topic kan dicht.

[GlowMouse]

Rekcor schreef op woensdag 31 januari 2007 @ 14:02:
Ik begrijp het. Eigenlijk is mijn query veel ingewikkelder dan SELECT *:

SQL:

SELECT producten.ID, producten.code, CONCAT(planten1.voornaam, " ", planten1.achternaam) AS 'Plant', verschijningsvormen2.naam_nl AS 'Verschijningsvorm', kleurcodes3.naam_nl AS 'Kleurcode', locaties4.naam AS 'Locatie', producten.standaardprijs, producten.voorraad, producten.dh_timestamp, producten.dh_userName
FROM producten
LEFT JOIN locaties AS locaties4 ON (producten.locatieId = locaties4.ID)
LEFT JOIN kleurcodes AS kleurcodes3 ON (producten.kleurcodeId = kleurcodes3.ID)
LEFT JOIN verschijningsvormen AS verschijningsvormen2 ON (producten.verschijningsvormId = verschijningsvormen2.ID)
LEFT JOIN planten AS planten1 ON (producten.plantId = planten1.ID)

en vroeg ik me af waarom die query niet optimaal was. Nu snap ik dat iedere query, die alle resultaten uit een tabel trekt, automatisch 'type=ALL' heeft voor de primary key.

Ik had in meerdere tutorials op internet gelezen dat dat echt heel slecht is, maar dat ging telkens over queries met WHERE restricties.

De query is nog steeds niet optimaal. MySQL gebruikt namelijk slechts een index per query per tabel. Veel van je joins worden nu dus niet geoptimaliseerd. Je kunt dus beter een index over meerdere velden definieren.

Goed, bedankt allemaal, topic kan dicht.

psst

[Rekcor]

psst

Oke, ik doe het netjes : mijn probleem bleek geen probleem, als een query alle resultaten van een tabel als resultaat heeft, zal EXPLAIN <query> 'type ALL' opleveren.