[MySQL] ORDER BY multiple tables optimalisatie

[Verwijderd]

SQL:

SELECT col1
FROM table1 t1
INNER JOIN table2 t2 USING id
INNER JOIN table3 t3 USING id
ORDER BY t2.col ASC, t3.col ASC

Het ORDER BY gedeelte gaat erg traag zodra er enkele duizenden records in de database staan. Dit is logisch, omdat de 'read_buffer_size' dan te klein is om alle records rechtstreeks vanuit het geheugen te ORDER'en en MySQL dan gebruik gaat maken van een bestand op de hdd.

De oplossing in dit geval is normaliter om indexes te plaatsen en eventueel de query uit te breiden met een USE INDEX commando. Helaas echter, zou de index in dit geval over meerdere tabellen uitgezet moeten worden. Aangezien dat niet kan, ben ik op zoek gegaan naar alternatieve oplossingen om de query toch snel te kunnen ORDER'en.

Helaas kon Google, etc. mij niet verder helpen. Iemand van jullie misschien?

[Verwijderd]

Het zou me lijken dat in dit geval aparte indexes ook het gewenste resultaat geven.

SQL:

CREATE INDEX table2_col_asc
  ON table2 (col ASC);

CREATE INDEX table3_col_asc
  ON table3 (col ASC);

Maar ja, ik ben PostgreSQL gewend, ik weet niet zeker na al die jaren of dingen in MySQL nog zo werken als ik me vaag herinner. :-)

[Speedener]

Lijkt mij dat je op zowel t2 als t3 een multi-column index moet zetten op ID + col. In die volgorde.

[cariolive23]

Speedener schreef op dinsdag 20 juli 2010 @ 19:44:
Lijkt mij dat je op zowel t2 als t3 een multi-column index moet zetten op ID + col. In die volgorde.

Waarom dat dan? "id" is waarschijnlijk de primary key en heeft dan al een index en daarnaast wordt er niet op id gesorteerd. Dus waarom dan een multi column index? Die is onbruikbaar.

Gewoon 2 aparte indexen, 1 index per tabel, en vervolgens met EXPLAIN even checken hoe e.e.a. nu wordt uitgevoerd. Als het goed is worden nu de indexen gebruikt om te sorteren.

[Speedener]

cariolive23 schreef op dinsdag 20 juli 2010 @ 20:01:
[...]

Waarom dat dan? "id" is waarschijnlijk de primary key en heeft dan al een index en daarnaast wordt er niet op id gesorteerd. Dus waarom dan een multi column index? Die is onbruikbaar.

Gewoon 2 aparte indexen, 1 index per tabel, en vervolgens met EXPLAIN even checken hoe e.e.a. nu wordt uitgevoerd. Als het goed is worden nu de indexen gebruikt om te sorteren.

Omdat er per tabel maar één index gebruikt kan worden. Dus moet er één index zijn voor zowel de matching als de ordering. En dan moet de matching column vooraan en de ordering column achteraan in de index.

[Verwijderd]

Speedener schreef op dinsdag 20 juli 2010 @ 20:09:
[...]

Omdat er per tabel maar één index gebruikt kan worden. Dus moet er één index zijn voor zowel de matching als de ordering. En dan moet de matching column vooraan en de ordering column achteraan in de index.

Ja dat was precies wat ik ook zat te denken... Echter, is de `id` een auto increment column, en voor zover ik weet kun je in het geval van InnoDB geen dual primary key instellen i.c.m. een auto increment. Toch?

[Speedener]

Verwijderd schreef op dinsdag 20 juli 2010 @ 20:58:
[...]

Ja dat was precies wat ik ook zat te denken... Echter, is de `id` een auto increment column, en voor zover ik weet kun je in het geval van InnoDB geen dual primary key instellen i.c.m. een auto increment. Toch?

Het hoeft / kan ook geen primary key te zijn. Het kan toch een 'gewone' key/index zijn? Dat werkt prima (99% zeker).

[Verwijderd]

Speedener schreef op dinsdag 20 juli 2010 @ 21:43:
[...]

Het hoeft / kan ook geen primary key te zijn. Het kan toch een 'gewone' key/index zijn? Dat werkt prima (99% zeker).

Als ik jouw post goed begrijp is het dus de bedoeling dat ik:

1. Een primary key + auto increment aanmaak voor `t1`.`id`
2. Een primary key + auto increment aanmaak voor `t2`.`id`
3. Een primary key + auto increment aanmaak voor `t3`.`id`
4. Een index plaats op (t2.id, t2.col) // en eventuele aanvullende columns waar de query mee van doen heeft
5. Een index plaats op (t3.id, t3.col) // en eventuele aanvullende columns waar de query mee van doen heeft
6. Een index plaats op de columns binnen `t1` waar de query mee van doen heeft
7. In de query per JOIN aangeef welke index gebruikt moet worden (in dit geval dus de index over de id+col+etc.)

Begrijp ik je dan goed?

[Speedener]

1, 2, 3, 6 zijn correct. 4 en 5 inderdaad in iedergeval de col 'id' vooraan en de col 'col' achteraan de index, dan kan de index gebruikt worden voor ordering. Voor 7 zou ik zeggen: niet de index hinten/forcen maar het MySQL laten uitzoeken (dat kan MySQL best zelf ). Als je 1 t/m 6 doet. En dan de query explained, dan zal je zien dat MySQL vanzelf de indexes pakt die je verwacht.

[Verwijderd]

Speedener schreef op dinsdag 20 juli 2010 @ 20:09:
[...]

Omdat er per tabel maar één index gebruikt kan worden. Dus moet er één index zijn voor zowel de matching als de ordering. En dan moet de matching column vooraan en de ordering column achteraan in de index.

MySQL kan al een hele tijd meerdere indexes per tabel gebruiken ;-)

[.oisyn]

Speedener schreef op dinsdag 20 juli 2010 @ 22:35:
1, 2, 3, 6 zijn correct. 4 en 5 inderdaad in iedergeval de col 'id' vooraan en de col 'col' achteraan de index, dan kan de index gebruikt worden voor ordering.

Dat lijkt me stug. De ordering is dan impliciet eerst op 'id', en dan op 'col', en dat is nou niet niet wat je wilt. De hele kolom 'id' is voor de ordering namelijk irrelevant. En daarom zal die index ook niet gebruik worden voor de sortering.

.edit: heb het ook net geprobeerd met een vergelijkbare situatie in een bepaalde database, en de explain zegt ook "Using temporary; Using filesort"

.edit2: bij mijn weten is er geen enkele manier om de sortering met een index efficient voor elkaar te krijgen. De enige manier waarbij een index efficient gebruikt kan worden is als hij (minimaal) overeen komt met de kolommen waarop je sorteert. Aangezien de kolommen waar je op sorteert sowieso meerdere tabellen beslaan ben je dus per definitie screwed omdat je een dergelijke index niet kunt maken.

[ Voor 42% gewijzigd door .oisyn op 21-07-2010 01:06 ]

[mjax]

Je zou ook in 1 van de 2 tabellen een veld kunnen toevoegen waarin je t2.col en t3.col slim samenvoegd, daar een index op aanmaakt, zodat je kunt sorteren op 1 kolom. Je moet dan wel de waarde in die extra kolom up-to-date houden bij wijzigingen in records in t2 of t3. En dat kan weer handig met een trigger (als je InnoDB gebruikt).

[jbdeiman]

Dan is het nog handiger om met een view te werken, die (temptable) je aan laat maken op het moment dat je de gegevens opvraagt. Dit is veel eenvoudiger dan met Triggers te gaan werken, de view gebruiken voor dit soort zaken is een behoorlijk stuk eenvoudiger.

[mjax]

Volgens mij kun je op een view geen index aanleggen, dus qua performance ga je er dan niet op vooruit.

[Verwijderd]

Nog andere ideeën om de boel toch sneller te krijgen? Dat moet toch gewoon kunnen lijkt me

[.oisyn]

Vaag, ik had gisteren nog iets geschreven maar blijkbaar nooit op "post" gedrukt . Anyway, wat ik zei was een index maken op (t2.col, t2.id) en een op (t3.id, t3.col). Of dit ook efficient selecteert hangt een beetje van het soort data af. Als elke id in t1 sowieso in t2 en t3 voorkomt dan maakt het niet zoveel uit, omdat er dan toch al over alle rijen van t2 gewandeld wordt. Maar er kan wel efficient gesorteerd worden, omdat de sortering al impliciet uit die indexen ontstaat (in de index van t2 staat alles gesorteert op t2.col, en in de index van t3 wordt per id gesorteerd op t3.col)

.edit: en blijkbaar is het in mysql dan ook handig om je query iets anders op te schrijven:

SQL:

SELECT t1.col1 
FROM table2 t2
INNER JOIN table3 t3 USING id 
INNER JOIN table1 t1 USING id 
ORDER BY t2.col ASC, t3.col ASC

.edit2: vaag, hij blijft wel nog steeds zeggen dat ie een temporary en een filesort nodig heeft. Ook die t1 gooit roet in het eten. Als ik t1 uit de query haal en dan niet sort is de sortering automatisch goed. Maar als ik de t1 er wel bij stop dan sorteert ie gewoon op id.

[ Voor 40% gewijzigd door .oisyn op 21-07-2010 11:37 ]

[The Eagle]

mjax schreef op woensdag 21 juli 2010 @ 07:10:
Volgens mij kun je op een view geen index aanleggen, dus qua performance ga je er dan niet op vooruit.

Wellicht zou je met een Materialized view nog wel wat kunnen - daar kun je namelijk WEL indexen op leggen
Enige nadeel is dat je een MVW om de zoveel tijd moet verversen en ik niet weet of MySQL deze ondersteunt.

[stappel_]

als je echt enkele duizenden records heb en je wil snelheid zou je misschien niet tot de 12e normaalvorm moeten gaan en dan data uit t2 of t3 moeten dupliceren in t1 of t2

[GlowMouse]

.oisyn schreef op woensdag 21 juli 2010 @ 11:16:
Vaag, ik had gisteren nog iets geschreven maar blijkbaar nooit op "post" gedrukt . Anyway, wat ik zei was een index maken op (t2.col, t2.id) en een op (t3.id, t3.col). [.. ] Maar er kan wel efficient gesorteerd worden, omdat de sortering al impliciet uit die indexen ontstaat (in de index van t2 staat alles gesorteert op t2.col, en in de index van t3 wordt per id gesorteerd op t3.col)

Dit klopt niet, om efficiënt te sorteren moet t3 per t2.col gesorteerd zijn op t3.col. Maar dan zou je de index ook gewoon op t3 in het geheel kunnen zetten omdat t2.col er dan als kolom in zou zitten.

Een order by over meerdere tabellen zonder 'using filesort' kun je vergeten in MySQL.

[b][message=34379502,noline]
.edit: en blijkbaar is het in mysql dan ook handig om je query iets anders op te schrijven:

SQL:

SELECT t1.col1 
FROM table2 t2
INNER JOIN table1 t1 USING id 
INNER JOIN table3 t3 USING id 
ORDER BY t2.col ASC, t3.col ASC

De JOIN-volgorde bij inner join kan zo wijzigen wanneer de index-statistieken wijzigen. Wil je je JOIN-volgorde zelf bepalen, gebruik dan STRAIGHT_JOIN.
Sowieso kan MySQL voor sortering alleen een index gebruiken van de tabel waar je alles op joint.

[ Voor 26% gewijzigd door GlowMouse op 21-07-2010 11:31 ]

[cariolive23]

Verwijderd schreef op woensdag 21 juli 2010 @ 10:49:
Nog andere ideeën om de boel toch sneller te krijgen? Dat moet toch gewoon kunnen lijkt me

Waar is het resultaat van EXPLAIN? Zonder deze gegevens kunnen we alleen maar raden waar het eventueel mis zou kunnen gaan. EXPLAIN zegt 100x meer dan een glazen bol...

[.oisyn]

GlowMouse schreef op woensdag 21 juli 2010 @ 11:29:
[...]

Dit klopt niet, om efficiënt te sorteren moet t3 per t2.col gesorteerd zijn op t3.col.

Dat is ie ook. De id's in t2 zijn gesorteerd per t2.col. Dus als je daar overheen loopt klopt de t2.col sortering. Voor elke id gaat ie vervolgens de rijen in t3 opzoeken die matchen met id. Daarvoor gebruikt ie de t3.id in de index, en alle rijen die daarbij horen staan gesorteerd op t3.col.

[GlowMouse]

.oisyn schreef op woensdag 21 juli 2010 @ 11:40:
[...]

Dat is ie ook. De id's in t2 zijn gesorteerd per t2.col. Dus als je daar overheen loopt klopt de t2.col sortering. Voor elke id gaat ie vervolgens de rijen in t3 opzoeken die matchen met id. Daarvoor gebruikt ie de t3.id in de index, en alle rijen die daarbij horen staan gesorteerd op t3.col.

Oneens, kijk maar naar dit voorbeeld.

code:

table2
col | id
1   | 1
1   | 2

code:

table3
id | col
1  | 5
2  | 1

Mocht t2.col wel uniek zijn, dan is MySQL er niet voor geoptimaliseerd afaik en gebruikt hij nog steeds een temp table. Maar omdat hij daar alles gesorteerd ingooit, zal het nog best vlot gaan.

cariolive23 schreef op woensdag 21 juli 2010 @ 11:39:
[...]

Waar is het resultaat van EXPLAIN? Zonder deze gegevens kunnen we alleen maar raden waar het eventueel mis zou kunnen gaan. EXPLAIN zegt 100x meer dan een glazen bol...

Tenzij er wat obvious dingen misgaan waardoor een join niet met een index lukt, heb je aan de gegeven info voldoende.

[ Voor 9% gewijzigd door GlowMouse op 21-07-2010 11:51 ]

[.oisyn]

GlowMouse schreef op woensdag 21 juli 2010 @ 11:48:
[...]

Oneens, kijk maar naar dit voorbeeld.

code:

table2
col | id
1   | 1
1   | 2

code:

table3
id | col
1  | 5
2  | 1

Ah ja, duh