[MySQL]join duurt te lang

[Verwijderd]

Hallo,

Mijn situatie is als volgt:

2 tabellen (user_settings en detailed_record, de eerste ongeveer 100.000 records de tweede ongeveer 900.000)

user_settings
-----------------
record_id (PK)
anomaly_id

detailed_record
-------------------
record_id (PK)
anomaly_id
car_id
first_activitation_date
nb_occurence

Ik wil nu uit detailed_record alle records hebben waarvan anomaly_id aanwezig is in user_settings.
Dit doe ik als volgt:

code:

select car_id, anomaly_id, first_activation_date, nb_occurence 
from detailed_record a 
inner join user_settings b 
on (a.anomaly_id = b.anomaly_id) 
where (a.first_activation_date >= '2005-12-4') and 
(a.first_activation_date <= '2005-12-30')

Dit geeft als resultaat: 457425 rijen in een kleine 1000 seconden.

ik heb het ook zo geprobeerd:

code:

select car_id, anomaly_id, first_activation_date, nb_occurence, 
from detailed_record a 
where ((a.first_activation_date >= '2005-12-4') and 
(a.first_activation_date <= '2005-12-30')) and 
a.anomaly_id in 
(select anomaly_id from usersettings)

Ik verwachte hetzelfde aantal rijen, maar ik krijg: 99014 rijen
in 300 seconden.


Ik heb het ook nog op een derde manier geprobeerd:

code:

select car_id, anomaly_id, first_activation_date, nb_occurence 
from detailed_record a 
where exists 
(select anomaly_id from user_settings b where ( 
(a.anomaly_id = b.anomaly_id) and (a.first_activation_date 
>= '2005-12-4') and (a.first_activation_date <= 
'2005-12-30')) 
)

Die geeft hetzelfde resultaat als tweede manier maar werkt wel trager.

Welke methode is nu juist?? (want het resultaat van de join en de andere methoden verschillen). En ook, hoe kan ik het rapper doen werken? Ik zit immers nog maar met een test databank, het aantal records neemt maandelijks toe.


Bedankt,

Bram

[whoami]

Hoe je het rapper doet werken ? Door goede indexen te leggen.

[x-force]

Je hebt anomaly_id niet als key/index staan. Als je deze toevoegd zal dit zeker helpen. Verder heb ik weinig ervaring met het optimaliseren van queries.

[ Voor 3% gewijzigd door x-force op 28-03-2006 14:41 ]

[Verwijderd]

ik kan eventueel anomaly_id van de tabel user_settings indexeren, maar aan de andere tabel kan ik niks wijzigen (niet toegelaten).
En enig idee wrm het resultaat van de queries niet overeenkomt?

[ Voor 20% gewijzigd door Verwijderd op 28-03-2006 14:43 ]

[whoami]

Aangezien je op activation-date filtert, is het ook handig om daar een index op te leggen.

(Je moet wel de kost van indexen afwegen natuurlijk, selects gaan sneller, maar inserts/updates gaan trager. Ik weet ook niet precies hoe MySQL omgaat met indexen; kan MySQL nu al méér dan één index gebruiken op één tabel binnen eenzelfde query ? ).

Ivm je verschil in rijen: wat gebeurt er als je je filter expressie niet in de where opneemt, maar bij de join-conditie zet ?

[JKVA]

whoami schreef op dinsdag 28 maart 2006 @ 15:04:

Ivm je verschil in rijen: wat gebeurt er als je je filter expressie niet in de where opneemt, maar bij de join-conditie zet ?

Dan moet je ook je index weer aanpassen eigenlijk, dat die meerdere rijen indexeert. Anders heb je er nog niks an.

Het is trouwens een inner join, dus waarschijnlijk maakt het geen verschil. Weet je zeker dat de data in de database ok is?

[ Voor 49% gewijzigd door JKVA op 28-03-2006 15:23 ]

[whoami]

JKVA schreef op dinsdag 28 maart 2006 @ 15:20:
[...]


Dan moet je ook je index weer aanpassen eigenlijk, dat die meerdere rijen indexeert. Anders heb je er nog niks an.

Eh

[Verwijderd]

als ik de selectie in de on-clausule plaats dan krijg ik nog meer records terug ...

Indexeren helpt slechts weinig, enkele tientallen seconden op 300 sec.
En ja indexeren op meerdere kolommen gaat.

[mjax]

Verwijderd schreef op dinsdag 28 maart 2006 @ 15:37:
als ik de selectie in de on-clausule plaats dan krijg ik nog meer records terug ...

Indexeren helpt slechts weinig, enkele tientallen seconden op 300 sec.
En ja indexeren op meerdere kolommen gaat.

Kun je een EXPLAIN van de query hier posten? Dan kunnen we zien hoe de optimizer de query wil uitvoeren en met welke indexen.

[whoami]

Ik vind het behoorlijk vreemd dat je een verschil krijgt in het aantal rijen dat je terugkrijgt.... IMHO zou dat toch hetzelfde moeten zijn...

Wellicht moet je ook eerst eens even je statistics updaten, vooraleer die nieuwe indexen echt effect hebben. Ik vraag me ook af wat het gevolg is van het plaatsen van quotes rond die datum. Ik denk dat MySQL ook je de inhoud van je datumveld eerst naar een varchar/char zal casten, wat ook vertragend werkt en waardoor er wellicht geen indexen gebruikt worden, maar dat ben ik niet zeker.
Bekijk anders eens het execution plan van die query ?

[Verwijderd]

id	select_type	table	type	possible_keys	key	key_len	ref	Extra
1	PRIMARY	a	ALL					Using where; Using filesort
2	DEPENDENT SUBQUERY	b	index

dit is den explain van de querie die gebruik maakt van de where anomaly_id in ( ... subquery... ) (die geeft namelijk het beste resultaat)

[ Voor 99% gewijzigd door Verwijderd op 28-03-2006 16:06 ]

[JKVA]

whoami schreef op dinsdag 28 maart 2006 @ 15:28:
[...]

Eh

Ik merk nu pas dat ik iets doms gezegd heb. Ik bedoel natuurlijk geen rijen, maar kolommen.

[JKVA]

Als je ergens "using filesort" ziet, is het mis, dan wordt er geen index gebruikt. Met tabel b lijkt me niks mis.

[zeroxcool]

JKVA schreef op dinsdag 28 maart 2006 @ 19:13:
Als je ergens "using filesort" ziet, is het mis, dan wordt er geen index gebruikt. Met tabel b lijkt me niks mis.

Filesorting an sich is niet erg. Dat doet MySQL volgens mij bij iedere sortering. Als je een filesort over 'maar' 100 records doet is dat niet erg.

Kijk vooral naar type, als die op ALL staat dan is het mis. En uiteraard naar het aantal rows.

[Verwijderd]

mja
probleem is dat ik aan tabel a niets mag wijzigen...

[DexterDee]

Indien de MySQL server een versie 5 of hoger is, kun je overwegen om een view te maken. Je maakt dan een soort virtuele table op basis van je query die je dan 'als ware het' een gewone table razendsnel kunt benaderen. Precies waarvoor database views bedoeld zijn

[P_de_B]

Zijn dat indexed views? Anders is het in feite niets meer dan een opgeslagen query en zal de snelheid niet omhoog gaan door het gebruik van de view.