[MySQL] Query zonder sort heel snel, met sort heel traag

[Verwijderd]

Hoi,

ik zit met een vervelend probleempje. Ik heb 2 tabellen waar de keys, indexen goed staan

1 tabel is heel groot: +- 1 000 000 records / 150MB
1 tabel is kleiner: +- 4000 records/ 500KB

de kleine heeft een 1-op-veel relatie met de grote.

Als ik een query zonder SORT uitvoer, duurt het slechts 0.005 sec voor ie uitgevoerd is
Dezelfde query MET SORT op eender welk veld duurt 0.5 sec, 100x trager dus.

Nu heb ik hier al wat van opgezocht en heb ik de mysql instelling sort_buffer_size veranderd van 2MB naar 4MB. Maar hij is nog steeds traag.

Enig idee wat er nog over het hoofd gezien is / of moet ik de sort_buffer_size drastisch verhogen?

thx

[Creepy]

Op welke velden sorteer je? Op welke velden liggen de indexen? Wat roept de EXPLAIN?

[ Voor 15% gewijzigd door Creepy op 05-01-2007 16:31 ]

[Verwijderd]

Creepy schreef op vrijdag 05 januari 2007 @ 16:30:
Op welke velden sorteer je? Op welke velden liggen de indexen? Wat roept de EXPLAIN?

De grote tabel bevat beschikbaarheidsdatums van elk huis, over de komende 2 jaar. Dus is hier de prim. key huiscode, datumvan, datumtot

De kleine tabel bevat informatie over huizen. met een huiscode als primary key en rest van de velden infomatie over dat huis.

als ik sorteer op huiscode heb ik het al zitten (nogthans de primary key).

Explain zeg over grote tabel:
select type: SIMPLE
table: grotetabel
type: range
possible keys: PRIMARY,datumvan,huiscode
key:datumvan
key_len:3
ref:NULL
rows:29053
extra: Using where; Using temporary; Using filesort

Over kleine tabel:
select type: SIMPLE
table: kleine tabel
type: ref
possible keys: PRIMARY
key:PRIMARY
key_len:62
ref:grotetabel.huiscode
rows:1
extra: Using where

Als ik dus geen sort gebruik, valt enkel "Using filesort" weg uit het overzicht, rest is hetzelfde

[ Voor 4% gewijzigd door Verwijderd op 05-01-2007 16:50 ]

[BKJ]

Verwijderd schreef op vrijdag 05 januari 2007 @ 16:47:
[...]


De grote tabel bevat beschikbaarheidsdatums van elk huis, over de komende 2 jaar. Dus is hier de prim. key huiscode, datumvan, datumtot

De kleine tabel bevat informatie over huizen. met een huiscode als primary key en rest van de velden infomatie over dat huis.

Even iets over je manier:

Kun je dan niet beter de data opslaan waarop de huizen NIET beschikbaar zijn? De rest is dan beschikbaar Een miljoen records voor een systeem dat data bijhoudt vind ik vrij veel...

[Creepy]

Ik vroeg of je de verschillende indexen e.d. wilde geven maar je noemt alleen de prim. keys op? Daarbij: heb je nu een losse select per tabel gedaan en daar een explain over of een explain van de daadwerkelijke query gedaan?

Ik gok dat een losse (extra) index op het veld waarom je sorteert nog wel eens kan helpen.

[JKVA]

Filesort is altijd slecht. Dat wil zeggen dat MySQL nog een keer over je resultaat heen moet om e.e.a. goed te zetten, ofwel sorteren.

Hier staan een aantal cases wanneer MySQL een filesort doet.
http://mysql.digipedia.pl...rder-by-optimization.html

[_Sunnyboy_]

Zo te zien heb je niet alleen veel records in de grote tabel, maar is elke record ook nog eens vrij groot (veel velden, grote velden). Dit kan leiden tot filesort.

Mogelijke oplossingen:
- index op het veld waarop je sorteert
- de grote tabel splitsen in 2 tabellen met een 1 op 1 relatie. Ik neem aan dat je in die query niet alle veden van de grote tabel nodig hebt. Door de grote tabel zo te splitsen dat je meestal de ene helft nodig hebt en alleen als je bijvoorbeeld details bekijkt of bewerkt de rest nodig hebt kan je de filesort mogelijk voorkomen.
Dit zie je bijvoorbeeld bij forums, waarbij de post over 2 tabellen is gesplitst. 1e tabel heeft relatie naar thread, poster, timestamp, subject etc. 2 tabel heeft alleen de postid en postbody.

[Verwijderd]

_Sunnyboy_ schreef op zaterdag 06 januari 2007 @ 12:22:
Zo te zien heb je niet alleen veel records in de grote tabel, maar is elke record ook nog eens vrij groot (veel velden, grote velden). Dit kan leiden tot filesort.

Mogelijke oplossingen:
- index op het veld waarop je sorteert
- de grote tabel splitsen in 2 tabellen met een 1 op 1 relatie. Ik neem aan dat je in die query niet alle veden van de grote tabel nodig hebt. Door de grote tabel zo te splitsen dat je meestal de ene helft nodig hebt en alleen als je bijvoorbeeld details bekijkt of bewerkt de rest nodig hebt kan je de filesort mogelijk voorkomen.
Dit zie je bijvoorbeeld bij forums, waarbij de post over 2 tabellen is gesplitst. 1e tabel heeft relatie naar thread, poster, timestamp, subject etc. 2 tabel heeft alleen de postid en postbody.

Nope, ik had al een index op het sorteerveld en een 1-op-1 relatie met de tabel dat kan natuurlijk niet omdat je meerdere beschikbaarheid hebt per huis.

De oplossing was:
Een optimize na het vullen van de tabel.
Elke tabel wordt namelijk elke dag opnieuw door xml gevuld. Als je blijkbaar een optimize na het vullen doet, dan gaat het pijlsnel.