[MySQL] Traag ondanks indexes

[Verwijderd]

Ik heb een MySQL database met daarin 2 tabellen, één met activiteiten en één met een afstanden tabel voor postcodes. Structuur van de afstanden tabel:

code:

id (primary)
postcode1 (INT(4), index)
postcode2 (INT(4), index)
afstand (INT, index)

De afstanden tabel bevat 16miljoen records met van iedere postcode de afstand tot iedere andere postcode.

De activiteiten tabel heeft ook een veld genaamd postcode1 met daarin ook een 4 cijferige code. met een index hier op. Hier staan ongeveer 15.000 records in. Ook de velden 'vast' en 'status' hebben een index.

Nu wil ik bezoekers via mijn website een postcode laten invoeren, en activiteiten binnen een bepaalde afstand tonen. Het probleem is dat ik met alleen de volgende query:

code:

SELECT a.id, a.titel, p.afstand FROM formulier_activiteiten a
LEFT JOIN formulier_postcodes p ON p.postcode1 = a.postcode1
AND p.postcode2 = '8911'
WHERE a.status =2
AND a.vast =1
GROUP BY a.id
LIMIT 0 , 30

Al een execution time van 14 secondes krijg. Hoe kan ik deze tijd optimalizeren, of is MySQL uberhaupt wel een geschikte database server om met zoveel records te werken?

De volgende queries komen overigens wel snel en direct terug:

code:

SELECT a.id, a.titel, p.afstand FROM formulier_activiteiten a
LEFT JOIN formulier_postcodes p ON p.postcode1 = a.postcode1
AND p.postcode2 = '8911'
GROUP BY a.id
LIMIT 0 , 30

code:

SELECT a.id, a.titel FROM formulier_activiteiten a
WHERE a.status =2
AND a.vast =1
LIMIT 0 , 30

[Noork]

Nou voor zover ik weet draait dit forum op MySql. Dus het lijkt me geen beperking van de database. Het is natuurlijk ook afhankelijk van de configuratie waar MySql op draait. Draait het b.v. op een Pentium 1 met 64MB geheugen of een of andere quad core met 8GB geheugen, waarbij de gehele db in het geheugen past. En natuurlijk van de Mysql instelling. Dus wat heb je voor configuratie staan? Hoe staat Mysql ingesteld?

Lijkt me dat je de boel verder ook wel kan optimalizeren. Google gewoon eens op mysql optimalization, en bekijk een en ander op dit forum.

[MueR]

Dus je voert een query uit op een tabel met 16 miljoen records, waarbij er misschien driehonderd duizend die bepaalde postcode in veld postcode2 hebben, en dan verwacht je dat het makkelijk en snel loopt? Ook een database server moet daar even over nadenken. Waarom voeg je niet ook alvast een maximum afstand toe? Lijkt me stug dat mensen die een postcode in Eindhoven invullen de activiteiten in Leeuwarden willen hebben, wel?

[Jan-E]

Verwijderd schreef op maandag 29 september 2008 @ 00:03:

code:

SELECT a.id, a.titel, p.afstand FROM formulier_activiteiten a
LEFT JOIN formulier_postcodes p ON p.postcode1 = a.postcode1
AND p.postcode2 = '8911'
WHERE a.status =2
AND a.vast =1
GROUP BY a.id
LIMIT 0 , 30

Probeer eens

code:

SELECT a.id, a.titel, p.afstand
FROM formulier_activiteiten a, formulier_postcodes p
WHERE a.status = 2
AND a.vast = 1
AND p.postcode1 = a.postcode1
AND p.postcode2 = '8911'
GROUP BY a.id
LIMIT 0 , 30

[Verwijderd]

Jan-E schreef op maandag 29 september 2008 @ 00:42:
[...]
Probeer eens

code:

SELECT a.id, a.titel, p.afstand
FROM formulier_activiteiten a, formulier_postcodes p
WHERE a.status = 2
AND a.vast = 1
AND p.postcode1 = a.postcode1
AND p.postcode2 = '8911'
GROUP BY a.id
LIMIT 0 , 30

Even traag, 10 secondes.

[Verwijderd]

MueR schreef op maandag 29 september 2008 @ 00:22:
Dus je voert een query uit op een tabel met 16 miljoen records, waarbij er misschien driehonderd duizend die bepaalde postcode in veld postcode2 hebben, en dan verwacht je dat het makkelijk en snel loopt? Ook een database server moet daar even over nadenken. Waarom voeg je niet ook alvast een maximum afstand toe? Lijkt me stug dat mensen die een postcode in Eindhoven invullen de activiteiten in Leeuwarden willen hebben, wel?

De maximum afstand zit hierin niet verwerkt omdat ik de query eenvoudig wou houden om te laten zien waarmee het al verkeerd gaat.

De server heeft prima hardware dus daar ligt het niet aan (Denk ik).

Het moet toch mogelijk zijn uit een tabel een aantal rijen te selecteren die matchen met een bepaalde postcode uit een andere tabel, en snel een resultaat terug te kunnen krijgen. Hoe zou ik dat anders kunnen oplossen dan een tabel met afstanden tussen 2 postcodes, als je moet kunnen zoeken op afstand.

[eamelink]

Doe eens "EXPLAIN" voor je query zetten; dan vertelt MySQL je wat hij doet (en dat zal wel een volledige tablescan zijn)

Verder heb je twee where clauses in je query, het lijkt mij dan ook uitermate handig om op die beide velden een gezamenlijke index te zetten. Wellicht moet je er ook nog de velden van je join aan toevoegen, maar dat vertelt de EXPLAIN je wel

Hoofdzaak is in ieder geval dat je meerdere velden in één index kan stoppen, en als je dat op de juiste manier doet komt het goed

[Verwijderd]

eamelink schreef op maandag 29 september 2008 @ 00:50:
Doe eens "EXPLAIN" voor je query zetten; dan vertelt MySQL je wat hij doet (en dat zal wel een volledige tablescan zijn)

Verder heb je twee where clauses in je query, het lijkt mij dan ook uitermate handig om op die beide velden een gezamenlijke index te zetten. Wellicht moet je er ook nog de velden van je join aan toevoegen, maar dat vertelt de EXPLAIN je wel

Hoofdzaak is in ieder geval dat je meerdere velden in één index kan stoppen, en als je dat op de juiste manier doet komt het goed

code:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE p ref postcode1,postcode2 postcode2 4 const 4103 Using temporary; Using filesort 
1 SIMPLE a ref postcode1,vast,status postcode1 4 funzi_cms.p.postcode1 7 Using where

Ik zit nu even te kijken hoe ik een index op meerdere velden plaats, bedankt voor je advies.

[Voutloos]

Je hebt dus een filesort te pakken. Indien geen volgorde nodig is, moet je indien je group by gebruikt, expliciet order by null neerzetten. Je hebt nu impliciet 'order by a.id' staan. [/ranzige mysql trivia].

De betere vraag is waarom je uberhaupt die group by nodig hebt, want het lijkt mij dat je activiteiten : postcode relatie zoals je hem nu hebt staan 1:1 is. Je huidige group by is overigens sowieso fout omdat je velden selecteert welke niet in de group by staan of het resultaat zijn van een aggregate function.

En last but not least: De 4 van int (4) zegt helemaal niets over het datatype (ja, dat is een grote wtf). Het is puur een hint voor de weergave op de command line. Oftewel, aangezien je maar max 9999 wil opslaan, kan je een kleiner integer type kiezen.

En voor elke kolom een enkelvoudige index neerzetten is ook een grove fout. Zie tientallen berichten op www.mysqlperformanceblog.com en dergelijke. Je geeft nu alles zonder goed na te denken een index, wat resulteert in veel overhead voor weinig nuttige indexen. Knikker die indexen weg en lees je eerst in over optimaliseren dmv explain, gecombineerde indexen en uberhaupt basiskennis indexen.

Zo, hier heb je een sloot tips.

[ Voor 21% gewijzigd door Voutloos op 29-09-2008 08:09 ]