[MySQL] Foreign keys en indexes

[mcdronkz]

Ik ben wat aan het experimenteren met InnoDB en foreign keys, maar ik loop tegen iets aan dat ik niet helemaal begrijp.

Ik gebruik de volgende tabellen:

mcdronkz_languages

code:

+------------+
| Field      |
+------------+
| laID       | 
| laLanguage |
+------------+

mcdronkz_newsitems

code:

+------------+
| Field      | 
+------------+
| niID       |
| niDateTime | 
| niTitle    | 
| niText     |
+------------+

mcdronkz_newsitems_trans

code:

+-------------+
| Field       | 
+-------------+
| nitItem     |
| nitLanguage |
| nitTitle    | 
| nitText     | 
+-------------+

Ik gebruik voor mcdronkz_newsitems_trans een compound primary key, die samengesteld is uit nitItem en nitLanguage. Zo krijg je dus een unieke combinatie.

Nu heb ik twee foreign keys in mcdronkz_newsitems_trans, van nitItem > niID en van nitLanguage > laID, logisch natuurlijk.

Maar als ik die twee keys aangemaakt heb krijg ik er een extra indice bij, een index voor nitLanguage. Waarom wordt er niet gewoon door beide foreign keys gebruik gemaakt van die compound primary key ? Kan dit niet ?

't Doet nu trouwens wel wat ik wil hoor, maar ik vroeg me gewoon af waarom er een extra index gemaakt moet worden.

[ Voor 5% gewijzigd door mcdronkz op 17-04-2008 01:30 ]

[DigiK-oz]

NitItem kan gebruik maken van de bestaande index, omdat dit het eerste veld is van de compound key. NitLanguage kan geen gebruik maken, omdat het het 2e deel is van de compound key, en het eerste deel niet wordt gebruikt bij de check op foreign key.

Zie het als een telefoonboek, dat is gesorteerd op naam, erg handig. Maar daar heb je niks aan als je alle namen wilt hebben die vanaf de 2e positie ANSEN heten (je weet de eerste letter niet)

Overigens MOET er niet per se een index op een foreign key liggen, maar kennelijk doet MySQL/InnoDB het standaard wel.

[ Voor 12% gewijzigd door DigiK-oz op 17-04-2008 07:36 ]

[Verwijderd]

mcdronkz schreef op donderdag 17 april 2008 @ 01:23:

Maar als ik die twee keys aangemaakt heb krijg ik er een extra indice bij, een index voor nitLanguage. Waarom wordt er niet gewoon door beide foreign keys gebruik gemaakt van die compound primary key ? Kan dit niet ?

Nee. De compound indexen zijn volgens mij zo opgebouwd dat er éérst gefilterd wordt op de eerste key, daarna wordt een overgebleven set gefiltert op de volgende. Ik heb dit probleem vooral eens gehad bij een zoekmachine-index. Daarbij waren bepaalde queries zo traag... dat was helemaal over toen ik ook een key toevoegde op de tweede FK. Blijkbaar is een index zo veel efficiënter.

Remember: een index is eigenlijk een lijstje om snel uit te zoeken van welke rijen je eigenlijk alle waarden wilt hebben. Hoe sneller je kunt filteren, hoe beter. Het verbaasde me eigenlijk ook niets dat dat zo optimaal mogelijk gaat. Alleen in de richting die is opgegeven.

[Voutloos]

En dit wordt ook inc. voorbeelden toegelicht in 7.4.4. Multiple-Column Indexes.