[mysql] Multi-row primarykey vs index, InnoDB vs MyIsam

[codeneos]

Ik ben bezig met het ontwerp van een aantal tables dat geoptimaliseerd dient te zijn voor Updates/Inserts/Deletes en selects. Waarbij het overgrote deel van de querys Inserts/Updates of Selects zal bedragen; 90%. De overig 10% zal deletes omvatten. Van de 90% is het aantal selects en inserts ongeveer 50/50 verdeeld. Het graat hier om middelgrote tables waar naar schatting 750.000 rows in zullen gaan.

Alle querys zijn van het type SIMPLE query, er worden geen joins uit gevoerd op de table of transactions.

Op basis van deze gegevens (grote hoeveelheid inserts en updates en MyIsam is ontworpen rond het gegeven dat 90% van de querys inserts zouden zijn [bron: mysql.org]) heb ik gekozen voor InnoDB, alleen ben ik mezelf niet zeker van de keuze omdat veel can de advance features niet gebruikt zijn. Was MyIsam dan toch geen betere keuze?

Alle querys hebben een WHERE cause op de `owner` column welke helaas niet unique en dus niet als primarykey kan dienen in de table, Elke table heeft ook nog een een colunm `slot` welke opzich zelf alleen unique is per owner en ook niet als primarykey kan dienen. De combinatie van `owner` en `slot` is wel altijd unique, dit is dan ook op dit moment de primarykey. Om mysql the helpen is er een index op de `owner` column aangemaakt.

Een explain query [EXPLAIN SELECT * FROM `p_items` WHERE `owner` = 6] geeft het volgen resultaat:

code:

mysql> EXPLAIN SELECT * FROM `p_items` WHERE `owner` =  6 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p_items
         type: ref
possible_keys: PRIMARY,owner
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 42
1 row in set (0.03 sec)

Mysql kiest voor de primarykey (voorspelbaar) maar is dit nu ook echt sneller? Had ik niet beter FORCE INDEX (owner) aan de query toe kunnen voegen, deze index is tenslotte alleen op de `owner` column.

[Voutloos]

Nee, dat kan je beter niet doen. Bij een samengestelde key kan je altijd ook efficient zoeken op alleen het 1e veld van die key. Volgens mij is je owner index hier redundant, dus weg er mee.

Verder is force index (en de andere index hints) een lapmiddel, een uiterste truc om in te zetten als anders een pertinent slechte index gebruikt wordt. In elke query een index hint neerzetten is imo bad practice, zo dom is de optimizer nou ook weer niet.

[codeneos]

Duidelijk dus gewoon de index op owner verwijderen. En wel bij de InnoDB engine blijven (innodb zoekt erg snel op primarykey, mede door het ontwerp (van InnoDB), dus ik denk vanwel)?

Ps. Ik heb geen idee hoe te zien wat het 1e veld van mijn primary key is, in de tables is `owner` wel de eerste column.

[ Voor 25% gewijzigd door codeneos op 18-06-2007 13:34 ]

[Voutloos]

Er zijn zat performance benchmarks te vinden, maar meestal maak je de keuze tussen deze 2 engines vanwege de features, bijvoorbeeld transacties (InnoDB) of fulltext search (MyISAM).
InnoDB voor deze tabel kan verder prima hoor.

codeneos schreef op maandag 18 juni 2007 @ 13:32:
Ps. Ik heb geen idee hoe te zien wat het 1e veld van mijn primary key is, in de tables is `owner` wel de eerste column.

Ik zei het inderdaad zo algemeen mogelijk, maar het feit dat de primary key gebruikt kan worden zegt toch ook al genoeg.