[SQL] Sortering link tabel

[Tjolk]

Ik ben bezig met een project (overgenomen van een collega die is vertrokken) om een oude dataset te converteren naar de inzichten van 2014. Was vroeger alles in 1 tabel geplempt met heel veel dubbelingen en kolommen die 90% van de tijd niets doen, wordt nu netjes alles in aparte tabellen geplaatst in groepen data die bij elkaar horen en gelinkt met koppeltabellen.
Het gaat in deze kwestie specifiek om een koppeltabel die users aan passen koppelt:

SQL:

delimiter $$

CREATE TABLE `link_user_pas` (
  `autonumber` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `pas_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`autonumber`),
  UNIQUE KEY `autonumber_UNIQUE` (`autonumber`),
  UNIQUE KEY `pas_id_user_id` (`pas_id`,`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1$$

Nu valt het me op als ik simpelweg de eerste 100 rijen selecteer ik een dataset krijg als:

autonumber	user_id	pas_id
5340	5340	97732
5929	5929	101458
6375	6375	102164
2970	2970	108476
12373	12373	108579
1386	1386	108988
1165	1165	112132
10901	10901	115993
8353	8353	118125
12444	12444	125496
6060	6060	125512

Sorteer ik specifiek op autonumber, dan krijg ik netjes autonumber 1 t/m 100.
PS: Het is op dit punt nog logisch dat een user_id identiek is aan een autonumber.

Nu is de hamvraag: waarom klopt die sortering niet standaard? Als ik het goed heb is bij InnoDB de sortering bepaald door de primary index.
Niet dat het nu héél belangrijk is ofzo, maar ik vraag me wel af hoe dit nu kan.

[Biersteker]

bedoel je niet ORDER BY `user_id` ASC|DESC ? (En als ik zo kijk. Lijkt eerder pasnummer de auto increment te hebben dan de `user_id`. )

[ Voor 50% gewijzigd door Biersteker op 05-05-2014 15:00 ]

[NMe]

Automatisch sorteringsgedrag is eigenlijk altijd undefined of dermate onvoorspelbaar dat het niet handig is om uit te gaan van een defaultsortering. Gewoon altijd even sorteren dus, als de sortering tenminste relevant is.

Er staat op de manualpagina die je linkt trouwens niks over sortering. De standaardsortering in MySQL is bij mijn weten ongeacht de gebruikte engine de invoervolgorde. Dus niet de volgorde van de PK, maar de volgorde waarin records zijn toegevoegd.

[ Voor 38% gewijzigd door NMe op 05-05-2014 15:04 ]

[Tjolk]

Biersteker schreef op maandag 05 mei 2014 @ 14:59:
bedoel je niet ORDER BY `user_id` ASC|DESC ?

Volgens de documentatie:

ORDER BY does not make sense for InnoDB tables because InnoDB always orders table rows according to the clustered index.

(En als ik zo kijk. Lijkt eerder pasnummer de auto increment te hebben dan de `user_id`. )

Beide waarden komen uit andere tabellen waarbij beide auto_increment hebben.

NMe schreef op maandag 05 mei 2014 @ 15:02:
Sorteringsgedrag is eigenlijk altijd undefined of dermate onvoorspelbaar dat het niet handig is om uit te gaan van een defaultsortering. Gewoon altijd even sorteren dus, als de sortering tenminste relevant is.

Mja, ik gebruik nogal veel MySQL Workbench, met rechtermuisklik -> select rows, limit 100. En dan steeds de sortering erbij typen is heul veul werk

[NMe]

Ger schreef op maandag 05 mei 2014 @ 15:05:
[...]

Volgens de documentatie:

[...]

Beetje een raar advies, het lijkt zo te suggereren dat order by nooit zin zou hebben bij een InnoDB-tabel, en dat kan niet kloppen.

[Tjolk]

Gaat over ALTER TABLE opdrachten, niet over SELECT opdrachten.

[NMe]

Ah, dat klinkt al een stuk logischer inderdaad.

[Tjolk]

Nog wat gevonden op Stackoverflow: http://stackoverflow.com/a/1949663

En dat klinkt als een logische verklaring, gezien de situatie.

[Russel88]

Waarom een PK op een autonumber veld?
En niet een pk op de beide ID velden en dan de autonumber veld droppen? Autonumber veld voegt toch weinig toe?

[NMe]

Dat heeft 'ie ook. Dat het veld "autonummer" heet wil niet zeggen dat het een auto incremented key is.

[Tjolk]

Russel88 schreef op maandag 05 mei 2014 @ 15:56:
Waarom een PK op een autonumber veld?
En niet een pk op de beide ID velden en dan de autonumber veld droppen? Autonumber veld voegt toch weinig toe?

Het hebben van een autonumber veld is een gewoonte van voornoemde collega die ikzelf ook nog steeds hanteer. De kolom wordt in principe voor niets anders gebruikt behalve dan voor het simpel inzichtelijk maken van gaten in de nummering.
Voor data tabellen heb je id's zoals het user id, maar voor link_ tabellen e.d. is een autonumber wel handig.

[Russel88]

NMe schreef op maandag 05 mei 2014 @ 16:00:
Dat heeft 'ie ook. Dat het veld "autonummer" heet wil niet zeggen dat het een auto incremented key is.

Ik ken de syntax van mysql niet zo.
Maar

SQL:

delimiter $$

CREATE TABLE `link_user_pas` (
  `autonumber` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `pas_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`autonumber`),
  UNIQUE KEY `autonumber_UNIQUE` (`autonumber`),
  UNIQUE KEY `pas_id_user_id` (`pas_id`,`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1$$

Suggereert toch dat AutoNumber veld een Auto_increment is en een primary key?

Ger schreef op maandag 05 mei 2014 @ 16:06:
[...]

Het hebben van een autonumber veld is een gewoonte van voornoemde collega die ikzelf ook nog steeds hanteer. De kolom wordt in principe voor niets anders gebruikt behalve dan voor het simpel inzichtelijk maken van gaten in de nummering.
Voor data tabellen heb je id's zoals het user id, maar voor link_ tabellen e.d. is een autonumber wel handig.

Voor mijn beeld. Is een link tabel hetzelfde als een koppel tabel?
Wat wil je dan doen met gaten in de nummering?
In je DB kan een gebruiker toch 1 of meerdere passen hebben? Pas kan verwijderd en toegevoegd worden? Dan heb je toch sowieso gaten?
Of is het van belang in welke volgorde de gebruiker de passen heeft ontvangen?

[NMe]

Russel88 schreef op maandag 05 mei 2014 @ 17:15:
[...]


Ik ken de syntax van mysql niet zo.
Maar

[...]

Suggereert toch dat AutoNumber veld een Auto_increment is en een primary key?

Oh, die had ik gemist. Dat lijkt me inderdaad verre van een goed idee als dat autonummer uit een andere tabel komt.