[MySQL] Warning bij index en primary key op zelfde veld

[BikkelZ]

Stel ik heb een primary key die is samengesteld uit drie foreign keys, dan heb ik dus die primary key, en zoals ik meestal doe, een index op iedere foreign key apart. Volgens mij is dat toch goed, omdat ik niet de primary key ineens pak, maar echt specifiek dat ene veld, en is de 'tweede' index dus zeker niet overbodig in verband met snelle joins naar andere tabellen.

Toch?

[MrBucket]

Ik kan me wel een situatie verzinnen waarom MySQL een warning zou kunnen geven, maar ik ben zelf niet zo thuis in MySQL, dus pin me er niet op vast

Ik weet niet of MySQL automatisch een index legt op de primary key, maar als dat zo is, dan heb je geen extra index nodig op het eerste veld uit deze samengestelde primary key. Dus stel dat je primary key gedefinieerd is als (veld1, veld2, veld3), dan kan de index over deze 3 velden ook worden gebruikt om alleen in veld1 te zoeken. Hij sorteert je records eerst op veld1, (voor gelijke waarden van veld 1) vervolgens op veld2, en (voor gelijke waarden voor veld1 en veld2) vervolgens op veld3.

Voor veld2 en veld3 heb je dus wel aparte indexen nodig; mijn vermoeden is dat als je de extra index op veld1 weghaalt, dat je ook geen warning meer krijgt.

[Creepy]

Misschien handig dat je aangeeft welke velden je nu preceis gebruikt als primarey key en waar je de indexen opzet samen met de warning? Met de situatie die je nu beschijft kan ik me niet echt voorstellen dat MySQL een warning geeft.

[Pete]

Zoals ik dit zie geeft phpMyAdmin je waarschijnlijk de warning. Dit is puur een hulpmiddel (omdat het vaak niet nodig is). Maar phpMyAdmin warnings kun je -als je beter weet- gewoon negeren.

[Voutloos]

Als je primary key op A,B,C hebt is het gewoon niet nodig om een key op alleen A te leggen. Een index op B en/of C apart kan wel nut hebben.

Overigens zeg je 'zoals ik meestal doe', maar dat klinkt een beetje erg dwangmatig, elke key moet een bewuste keuze zijn, onnodige keys zijn gewoon zonde van de storage ruimte en de tijd die verloren gaat met het bijhouden van de keys.

phsmit schreef op dinsdag 17 april 2007 @ 21:19:
Zoals ik dit zie geeft phpMyAdmin je waarschijnlijk de warning. Dit is puur een hulpmiddel (omdat het vaak niet nodig is). Maar phpMyAdmin warnings kun je -als je beter weet- gewoon negeren.

Er zijn een paar situaties waarbij phpMyAdmin deze warning ten onrechte geeft (bijvoorbeeld gelijkende keys qua kolommen, maar verschillend omdat de ene key beperkte veldlengtes gebruikt), maar deze warning moet je imo wel serieus nemen. Serieus nemen als in goed erover na denken, niet per se klakkeloos 1 van de indexen weghalen zodat phpMyAdmin niet zeurt.

Overigens is ook de gebruikte engine relevante informatie, innoDB doet een aantal extra dingen met de primary key.

[ Voor 51% gewijzigd door Voutloos op 17-04-2007 21:39 ]

[BikkelZ]

Het gaat inderdaad om PHPMyAdmin, niet MySQL zelf! Sorry voor de verwarring!

Creepy schreef op dinsdag 17 april 2007 @ 20:12:
Misschien handig dat je aangeeft welke velden je nu preceis gebruikt als primarey key en waar je de indexen opzet samen met de warning? Met de situatie die je nu beschijft kan ik me niet echt voorstellen dat MySQL een warning geeft.

Dit is de DB:

SQL:

CREATE TABLE `actie_medewerker` (
  `medewerker_id` int(11) NOT NULL,
  `actie_id` int(11) NOT NULL,
  `aantal` int(11) NOT NULL,
  UNIQUE KEY `beller_id_2` (`medewerker_id`,`actie_id`),
  KEY `beller_id` (`medewerker_id`),
  KEY `actie_id` (`actie_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

[Voutloos]

De index op medewerker_id is hier inderdaad redundant.

[BikkelZ]

Voutloos schreef op vrijdag 20 april 2007 @ 12:59:
De index op medewerker_id is hier inderdaad redundant.

Waarom? (foreign key)

[Voutloos]

Queries welke baat hebben bij een index op medewerker_id kunnen gebruik maken van die samengestelde index, deze index is gewoon overbodig. Dat het een foreign key is maakt niet zo zeer uit in dit verhaal.

[BikkelZ]

Ik ben nog wat gaan Googlen en ben het volgende tegen gekomen:

http://www.xaprb.com/blog...ysql-index-optimizations/

Antwoord op precies dezelfde vraag als ik had:

That’s a great question. It depends on whether the index has a column leftmost or not. If a table with columns (a, b, c) has an index on columns (a, b) it doesn’t need an index on column (a) alone, because it is already the leftmost column in an existing index. MySQL can use the leftmost prefix of any index efficiently.

However, if you do a query with just column b in the WHERE clause, it cannot use the (a, b) index. In this case you may need to add an index on just (b).

....dat zou dus betekenen dat ik actie_id wel moet indexen maar medewerker_id dus niet.

Er staan nog wat meer interessante links in de comments, dus ik blijf nog wel even zoet met lezen

[Voutloos]

Goed dat je aan het lezen bent, maar dit had ik al gezegd he.

Voutloos schreef op dinsdag 17 april 2007 @ 21:35:
Als je primary key op A,B,C hebt is het gewoon niet nodig om een key op alleen A te leggen. Een index op B en/of C apart kan wel nut hebben.

Overigens is een site als http://www.mysqlperformanceblog.com/ ook wel redelijk leesvoer. Het is brak Engels en soms staat er een onzin postje tussen, maar de artikelen over indexen zijn wel goed.

[BikkelZ]

Voutloos schreef op dinsdag 24 april 2007 @ 07:50:
Goed dat je aan het lezen bent, maar dit had ik al gezegd he.

Inderdaad blijkt jouw post dan ook helemaal correct te zijn! Maar ik ben natuurlijk ook een beetje eigenwijs

Voutloos schreef op dinsdag 24 april 2007 @ 07:50:Overigens is een site als http://www.mysqlperformanceblog.com/ ook wel redelijk leesvoer. Het is brak Engels en soms staat er een onzin postje tussen, maar de artikelen over indexen zijn wel goed.

Die site was ik al tegen gekomen inderdaad, weer een hoop van geleerd

Wat betreft dat 'dwangmatige' indexen aanleggen: het gaat om de scharnieren tussen tabellen die ik vaak koppel, en dan altijd een pri key <-> for key relatie. Volgens mij is het ook zelfs zo dat InnoDB je niet eens een relatie laat aanleggen zonder dat je eerst een index hebt op de kolom die je gebruikt.

[ Voor 18% gewijzigd door BikkelZ op 24-04-2007 11:45 ]