[MySQL] INNODB tabel met multiple-column index en auto_incr

[EmilneM]

Omdat ik veel met transactions werk heb ik in een MySQL-database gekozen voor INNODB-tabellen (MyISAM ondersteunt namelijk geen transactions).

In deze database bevindt zich een tabel 'klanten'. Deze tabel bevat 'klanten' voor meerdere 'bedrijven':

BedrijfId

KlantId

Een belangrijke eis van de opdrachtgever is dat het KlantId oploopt per BedrijfId (VAN DEZE EIS MAG NIET WORDEN AFGEWEKEN). Hiervoor moet dus een multiple-column primary key aangemaakt worden, met een auto_increment op de KlantId-column:

BedrijfId	KlantId
1	1
1	2
1	3
2	1
2	2

Probleem is hierbij dat INNODB een auto_increment in een multiple-column key situatie niet ondersteunt... Hierom heb ik het volgende uitgedacht bij de inserts:

SQL:

INSERT INTO klanten (
     BedrijfId,
     KlantId
)
SELECT
     [BedrijfId_Value],
     (
          SELECT
               (CASE WHEN MAX(KlantId) <> 'NULL' THEN 
                 MAX(KlantId) ELSE '0' END) + 1
          FROM
               klanten
          WHERE
               BedrijfId = [BedrijfId_Value]
          FOR UPDATE
     )
;

Het lijkt te werken op deze manier, vind de query alleen nogal groot. Ik heb een aantal vragen:

1. Volgens mij is de snelheid niet (veel) lager dan in een MyISAM situatie met auto_increment (Volgens mij wordt er met auto_increment namelijk altijd een MAX(Id) query uitgevoerd op de achtergrond). Zijn jullie dit met me eens?

2. Is er een efficientere manier?

3. Dek ik alles af wat betreft locking? Ook in multi-user situaties?

[Icelus]

Kun je geen primary key op de twee velden maken plus een auto increment?
Voorbeeld / Resultaat

[ Voor 22% gewijzigd door Icelus op 06-04-2007 14:35 ]

[EmilneM]

Icelus schreef op vrijdag 06 april 2007 @ 14:34:
Kun je geen primary key op de twee velden maken plus een auto increment?
Voorbeeld / Resultaat

Bij een myISAM-tabel kan dit inderdaad. Echter, omdat ik veel gebruik maak van transactions kan ik myISAM niet gebruiken als tabeltype (myISAM ondersteunt geen transactions).

INNODB ondersteunt deze manier (jammergenoeg) niet...

[Icelus]

EmilneM schreef op vrijdag 06 april 2007 @ 14:41:
[...]


Bij een myISAM-tabel kan dit inderdaad. Echter, omdat ik veel gebruik maak van transactions kan ik myISAM niet gebruiken als tabeltype (myISAM ondersteunt geen transactions).

INNODB ondersteunt deze manier (jammergenoeg) niet...

Weet je het zeker?
Kan op dit moment geen InnoDB maken, op MySQL' site staat:

Does not work, while the script below works fine if you change the order of the primary key constraint to where the AUTO_INCREMENT column is listed first:

SQL:

CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id,grp)
);

InnoDB seems to require the AUTO_INCREMENT first.

Bron

[EmilneM]

Icelus schreef op vrijdag 06 april 2007 @ 14:49:
[...]
Weet je het zeker?
Kan op dit moment geen InnoDB maken, op MySQL' site staat:
[...]
Bron

Op bovenstaande manier is de volgorde van primary key Id-Grp i.p.v. Grp-Id. Dit is dan het resultaat:

Id	Grp
1	fish
2	fish
3	mammal
4	fish
5	bird

in plaats van (wat het zou moeten zijn):

Grp	Id
fish	1
fish	2
mammal	1
fish	3
bird	1

Op het moment dat ik de kolommen omdraai (Grp, Id) ipv (Id, Grp) staat het DBMS dit niet toe.

[frickY]

Volgens mij wordt er bij een auto-inrement geen MAX() gedaan ,maar ligt de voorgaant ingevoerde auto-increment value vast in een index/tabelstructuur.

De opdrachtgever zou geen enkele last mogelijk hebben van wat voor ID's jij in je tabel gebruikt. Als hij een ID als presentatie wilt gebruiken zou ik zelf hiervoor een aparte kolom gebruiken welke ik per bedrijf opnieuw nummer.
Want wat zou jij nu bijvoorbeeld doen als een klant verwijderd wordt? Hou je het gat of update je het record, en al haar relaties, naar een hoger ID?

[EmilneM]

frickY schreef op vrijdag 06 april 2007 @ 15:15:
Als hij een ID als presentatie wilt gebruiken zou ik zelf hiervoor een aparte kolom gebruiken welke ik per bedrijf opnieuw nummer.

Gaat idd om presentatie. Echter, voor die aparte kolom geldt dan toch hetzelfde vraagstuk? Of is er dan een snellere manier als de query in mijn startpost?

[Icelus]

EmilneM schreef op vrijdag 06 april 2007 @ 15:06:
[...]


Op bovenstaande manier is de volgorde van primary key Id-Grp i.p.v. Grp-Id. Dit is dan het resultaat:

Id	Grp
1	fish
2	fish
3	mammal
4	fish
5	bird

in plaats van (wat het zou moeten zijn):

Grp	Id
fish	1
fish	2
mammal	1
fish	3
bird	1

Op het moment dat ik de kolommen omdraai (Grp, Id) ipv (Id, Grp) staat het DBMS dit niet toe.

Ja, zie het...
BerkleyDB is ook geen optie/mogelijkheid?

[EmilneM]

Icelus schreef op vrijdag 06 april 2007 @ 15:38:
[...]
Ja, zie het...
BerkleyDB is ook geen optie/mogelijkheid?

Nee, zit (nog) vast aan MySQL 4.1 en die ondersteunt geen BDB. INNODB is dus enige mogelijkheid (en daarnaast weet ik niet of BDB wat betreft auto_increment anders werkt dan INNODB).

[ Voor 13% gewijzigd door EmilneM op 06-04-2007 15:43 ]

[ACM]

EmilneM schreef op vrijdag 06 april 2007 @ 14:11:
Probleem is hierbij dat INNODB een auto_increment in een multiple-column key situatie niet ondersteunt... Hierom heb ik het volgende uitgedacht bij de inserts:

Volgens mij kan MySQL ook met MyISAM deze constructie niet.

Het lijkt te werken op deze manier, vind de query alleen nogal groot. Ik heb een aantal vragen:

Formaat van een query is op zich natuurlijk niet zo relevant.

1. Volgens mij is de snelheid niet (veel) lager dan in een MyISAM situatie met auto_increment (Volgens mij wordt er met auto_increment namelijk altijd een MAX(Id) query uitgevoerd op de achtergrond). Zijn jullie dit met me eens?

Het lijkt me niet dat die select de ergste performance van je totale insert-tijd behelst, maar als je je puur op dat stukje van de operatie concentreert gok ik dat het 'significant' langzamer is. De laatste waarde van de auto_increment wordt ergens opgeslagen, terwijl jij eerst nog allerlei locks moet plaatsen en een select uitvoert.
Maar is de performance uberhuapt zo relevant? Wordt dit je meestgebruikte query, wordt ie onderdeel van een of meerdere kritieke paden? Lijkt mij persoonlijk van niet. En dan zou ik me over de performance niet te veel zorgen maken.

2. Is er een efficientere manier?

Volgens mij niet. Tenzij je de eis iets mag aanpassen, want ook een algemene auto_increment is strict oplopend per bedrijf, alleen weet je het verschil tussen de oplopende waarden niet.
Mogelijk is het wel efficienter om een losse identity-tabel bij te houden met 1 record per klant, waarin je enkel het laatst uitgegeven id, waardoor je je select-for-update op een heel klein tabelletje op slechts 1 record los kan laten. Maar ik zou die moeite zelf niet zo gauw doen.

3. Dek ik alles af wat betreft locking? Ook in multi-user situaties?

Ik vind for update locks altijd wat mistig, maar volgens mij wel. Je zou eventueel de transaction isolation level nog op serializable kunnen zetten tijdens deze query, maar ik weet niet in hoeverre dat je performance beinvloed.