[MySQL] Crash bij uitgebreide query*

[Verwijderd]

Het volgende probleem doet zich voor. Ik heb een database draaien met een aantal tabellen waaronder een postcode tabel met daarin foreign keys naar de tabellen plaats, gemeente, regio etc.

Nu doe ik de volgende query:

select postcode.postcode_id from postcode inner join plaats on postcode.plaats_id = plaats.plaats_id inner join gemeente on postcode.gemeente_id = gemeente.gemeente_id inner join regio on postcode.regio_id = regio.regio_id

Mijn hele computer freezed als ik deze query uitvoert in MYSQL. Enige optie is 2 sec de powerknop ingedrukt houden.

Deze query draait prima onder dezelfde database die op Ms SQL Server draait.. Dus het heeft iets met MySQL te maken..

Alle tabellen zijn geindexeerd. Het probleem ligt hem waarschijnlijk in het feit dat hij duizenden records terug krijgt, maar je zou toch denken dat mYSQL dat gewoon aankan.

[Cyphax]

Welk OS? Moet niet zo heel veel uitmaken want dit moet inderdaad niet je PC crashen. Hoge load is 1 ding maar dit klopt inderdaad niet.
't is ook maar een simpele query verder. Hier is zonder meer info niet zoveel van te zeggen gok ik zo. Kijk even in de logs van je systeem..

[Verwijderd]

Verwijderd schreef op maandag 02 mei 2005 @ 11:49:
Alle tabellen zijn geindexeerd. Het probleem ligt hem waarschijnlijk in het feit dat hij duizenden records terug krijgt, maar je zou toch denken dat mYSQL dat gewoon aankan.

Heb je al getest met een LIMIT erachter? Dan weet je iig of het hier aan ligt.

[T-MOB]

Je zou met een EXPLAIN even kunnen kijken of er daadwerkelijk indexen worden gebruikt. Daarnaast kun je door gewoon even te testen met een LIMIT van pak 'm beet 1000 records kijken of het in de query zit of in het aantal records.

Bij grote hoeveelheden records kan een unbuffered query ook helpen. Dat scheelt dan nogal in de hoeveelheid benodigd geheugen.

[Mir]

Is dit niet mooier om te doen?

(en kan wellicht het crashen tegengaan?)

code:

select
    pc.postcode_id
from
    postcode pc
inner join
    plaats pl,
    gemeente g,
    regio r
on
    pc.plaats_id = pl.plaats_id
AND pc.gemeente_id = g.gemeente_id
AND pc.regio_id = r.regio_id

[Verwijderd]

Ik draai zelf XP, maar hij geeft dezelfde fout bij m'n provider (linux servers) (daar hangen overigens de servers niet maar krijg ik een dikke timout).. Ik zal het eens proberen met een limit..

Het p.regio_id = r.regio_id etc heb ik al geprobeerd met hetzelfde resultaaat.

[ Voor 5% gewijzigd door Verwijderd op 02-05-2005 12:07 ]

[Creepy]

En je indexen zijn wel zo gelegd dat je queries er gebruik van kunnen maken? (lees: op de velden van je where). Je kan checken met de EXPLAIN functie of dat zo is.

[djluc]

Hoe groot zijn de tabellen? En gebruik je ook de juiste data-typen in je database?

[Verwijderd]

Dit is de query die ik nu uitvoer:

code:

EXPLAIN
SELECT garage.garage_id
FROM garage_dienst
INNER JOIN dienst
  ON garage_dienst.dienst_id = dienst.dienst_id
RIGHT OUTER JOIN gemeente
  INNER JOIN postcode
    INNER JOIN plaats
      ON postcode.plaats_id = plaats.plaats_id
    INNER JOIN provincie
      ON postcode.provincie_id = provincie.provincie_id
    INNER JOIN regio
      ON postcode.regio_id = regio.regio_id
    ON gemeente.gemeente_id = postcode.gemeente_id
  RIGHT OUTER JOIN garage_auto
    INNER JOIN auto
      ON garage_auto.auto_id = auto.auto_id
    RIGHT OUTER JOIN garage
      ON garage_auto.garage_id = garage.garage_id
    ON plaats.plaats_id = garage.plaats_id
  ON garage_dienst.garage_id = garage.garage_id
WHERE auto.auto_id = 5
GROUP BY garage.garage_id

Daaruit komt de volgende tabel:

table

type

possible_keys

key

key_len

ref

rows

Extra

auto

const

PRIMARY

PRIMARY

4

const

1

Using

index;

Using

temporary;

Using

filesort

gemeente

index

NULL

PRIMARY

4

NULL

468

Using

index

postcode

index

INDEX

INDEX

20

NULL

4770

Using

index

provincie

eq_ref

PRIMARY

PRIMARY

4

postcode.provincie_id

1

Using

index

garage_auto

index

INDEX

INDEX

10

NULL

2403

Using

where;

Using

index

garage_dienst

index

NULL

INDEX

10

NULL

16021

Using

index

plaats

eq_ref

PRIMARY

PRIMARY

4

postcode.plaats_id

1

Using

index

garage

eq_ref

PRIMARY

PRIMARY

4

garage_auto.garage_id

1

dienst

eq_ref

PRIMARY

PRIMARY

4

garage_dienst.dienst_id

1

Using

index

regio

eq_ref

PRIMARY

PRIMARY

4

postcode.regio_id

1

Using

index

Mijn excuses voor het even niet netjes in een tabel zetten.

[ Voor 141% gewijzigd door NMe op 02-05-2005 13:41 . Reden: Je had in plaats van excuses maken ook gewoon netjes je post kunnen opmaken. ;) ]

[T-MOB]

Verwijderd schreef op maandag 02 mei 2005 @ 12:50:
de postcode tabel is 12 velden lang waarvan 5 foreign keys op datatype int(11)..
er zitten 4800 records in..

Een int(11) is natuurlijk wáy overkill voor postcodes. In Nederland zijn er maximaal 10.000*26² postcodes mogelijk (int(7) voldoet qua PC-id). Er zijn iets minder dan 500 gemeenten (smallint(3) voor gemeente-id)) en dus nog minder regio's (tinyint(3) allicht voldoende). Qua plaatsen weet ik niet exact hoeveel, maar met 10.000 (smallint(4)) zit je redelijk safe lijkt me zo.

Als je met 4800 records al een time-out krijgt zit er trouwens iets gruwelijk mis in je database. Of je hebt héél weinig geheugen. Ter illustratie: mijn K6-III met 228Mb ram crasht pas als je 400.000+ records met 300kolommen probeert op te halen.

Heb je al geprobeerd om een EXPLAIN query te doen, welke indexen gebruikt MySQL wanneer je de query runt?

[Verwijderd]

ik heb het hiervoor met gewoon INT(4) geprobeerd, maar aan hardware tekortkomingen ligt het niet.. Bij mijn provider (servers met 4GB geheugen) krijg ik hetzelfde probleem. Ik heb EXPLAIN gedaan en dat resulteerde in bovengenoemde tabel.

Maar begrijp ik het goed dat hij uit bovenstaande kolom een resultaat aan rows van het produkt van alle rows wil ophalen? Dat zouden er inderdaad veels te veel zijn. Maar wat vreemd dat MS SQL Server wel gewoon op deze query een prima resultaat geeft in <1sec.

[ Voor 36% gewijzigd door Verwijderd op 02-05-2005 13:43 ]

[curry684]

Je mist een index op garage, en probeer zeker in MySQL zoveel mogelijk LEFT joins ipv RIGHT joins te gebruiken (right joins correct optimaliseren is niet MySQL's sterkste kant en zou een verklaring kunnen zijn voor het probleem).

Verwijderd schreef op maandag 02 mei 2005 @ 13:38:
Maar begrijp ik het goed dat hij uit bovenstaande kolom een resultaat aan rows van het produkt van alle rows wil ophalen? Dat zouden er inderdaad veels te veel zijn.

Vooral 'using filesort' en 'using temporary' zijn erg pijnlijk in je EXPLAIN, en kunnen in de query die je geeft best wel eens betekenen dat er gigabytes aan data op disk wordt geschreven alvorens deze zo traag mogelijk te sorteren. In de MySQL manual staan goede voorstellen over hoe je filesort en temporary kunt elimineren.

Maar wat vreemd dat MS SQL Server wel gewoon op deze query een prima resultaat geeft in <1sec.

Je moet een stuk speelgoed ook niet met een professioneel DBMS vergelijken

[ Voor 64% gewijzigd door curry684 op 02-05-2005 13:48 ]

[Verwijderd]

Bedankt.. ik zal de opties filesort even bekijken..

De reden dat er op garage geen INDEX zit is omdat deze garage_id in geen enkele andere gebruikte tabel uit de query staat. En in garage is hij al PK

[ Voor 7% gewijzigd door Verwijderd op 02-05-2005 14:05 ]

[curry684]

Algemene stelregel voor 99% van de simpele databases: beter teveel indexen dan te weinig Zeker op alle frequent uitgevoerde SELECTs moet gewoon iedere join een index gebruiken (table scans doen pijnnnnn).

[Verwijderd]

phpMyAdmin zegt dat je niet op een kolom zowel een PK als een INDEX mag gebruiken..

Overigens.. net even geprobeerd alle inner joins te vervangen voor a.key = b.key en de right joins vervangen voor left join... ziet er al een stuk beter uit!!! hij crasht in ieder geval niet meer..

Algemene stelregel: queries generereerd door MS SQL zomaar posten in MySQL werkt niet altijd :-)

[curry684]

Verwijderd schreef op maandag 02 mei 2005 @ 14:22:
phpMyAdmin zegt dat je niet op een kolom zowel een PK als een INDEX mag gebruiken..

Nogal wiedes, een PK is per definitie een index in MySQL Daarom moet je ook met multi-column indexes werken als je binnen MySQL's limitaties wil blijven. Het feit dat MySQL per gejoinde tabel maximaal 1 index kan toepassen gaat je heel snel op de zenuwen zitten

Overigens.. net even geprobeerd alle inner joins te vervangen voor a.key = b.key

Da's equivalent?

en de right joins vervangen voor left join...

Je hebt hopelijk de hele query structureel herschreven daarvoor en niet simpel find/replace gedaan?

Algemene stelregel: queries generereerd door MS SQL zomaar posten in MySQL werkt niet altijd :-)

Nog een paar stelregels:
• Right join in MySQL is ruk.
• Indexes in MySQL zijn ruk.
• Rag er altijd een explain doorheen, en zorg voor combined indexes om filesorts en temporaries te elimineren

Stel dat je moet joinen op een tabel waar je vervolgens ook op moet filteren. Dan moet je dus een index leggen op (key, sortfield) zodat ie eerst kan joinen op het eerste deel van de index, en daarna op de subindex de resultaten kan filteren voordat ze in de resultset komen (en er dus een filesort nodig is). 2 afzonderlijke indexen voor die 2 operaties gebruiken kan ie tenslotte niet

[Verwijderd]

Overigens.. net even geprobeerd alle inner joins te vervangen voor a.key = b.key
Da's equivalent?

Ja. (toch?).. nouja.. het gaat iets verder dan a=b...

garage.plaats_id = plaats.plaats_id zou hetzeflde moeten zijn als inner join plaats on garage.plaats_id = plaats.plaats_id

[ Voor 43% gewijzigd door Verwijderd op 02-05-2005 14:53 ]

[curry684]

Verwijderd schreef op maandag 02 mei 2005 @ 14:48:
Overigens.. net even geprobeerd alle inner joins te vervangen voor a.key = b.key
Da's equivalent?

Ja. (toch?).. nouja.. het gaat iets verder dan a=b...

garage.plaats_id = plaats.plaats_id zou hetzeflde moeten zijn als inner join plaats on garage.plaats_id = plaats.plaats_id

Er zijn 2 (volstrekst equivalente) manieren om een inner join te gebruiken:

SQL:

select * from tabelA, tabelB
where tabelA.foreign = tabelB.primary

En de ANSI-manier:

SQL:

select * from tabelA
inner join tabelB on tabelA.foreign = tabelB.primary

Needless to say dien je zoveel mogelijk de ANSI-manier te gebruiken, al is het maar omdat je de deprecated eerste syntax niet kunt combineren met outer joins en deze veel minder overzichtelijk is doordat je de te joinen tabel fysiek scheidt van de joinende conditie. Tis verder gewoon identiek en zal hetzelfde uitgevoerd worden tho.

[Verwijderd]

mijn voorkeur gaat eigenlijk ook uit naar de INNER JOIN manier... mijn meester op school vond dat ook.