Hallo,

De volgende query duurt ongeveer 2,5 seconde om uit te voeren. Dit zou ik graag willen optimaliseren, maar hierbij loop ik vast.

PHP Code:

SELECT
     // enkele velden van de contactgegevens tabel​
FROM
     contactpersonen AS c
      LEFT JOIN
        (SELECT * FROM `contactgegevens` WHERE `status` != '9' ORDER BY `id` DESC) as `cg`
         ON cg.contact_id = c.id
WHERE
    // enkele voorwaarden waaraan de contactgegevens moeten voldoen
GROUP BY
c.id 


Tabel contactpersonen: Een lijst met gebruikers.
Tabel contactgegevens: Een lijst van alle contactgegevens per gebruiker. Bij wijziging wordt er een nieuwe record toegevoegd in plaats van de bestaande gewijzigd om hiermee een geschiedenis op te bouwen.

Het probleem is dat de LEFT JOIN subquery alle contactgegevens erbij pakt, in plaats van de gegevens die behoren bij de contactpersoon.

Het uiteindelijke resultaat is dus goed, maar de query duurt te lang om uit te voeren. Hopelijk kunnen jullie hiermee helpen.

Groet.

Wat is de grote van de database en tabellen?

Origineel gepost door Leander

Wat is de grote van de database en tabellen?

Bedankt voor je reactie.

contactpersonen

1,070

InnoDB

utf8_unicode_ci

96KiB

contactgegevens

1,803

InnoDB

utf8_unicode_ci

352KiB

Heb je nog meer info nodig?


Nog even ter aanvulling. Van de contactpersonen die hij moet laten zien, moet de query dus de laatste rij met contactgegevens gebruiken. Vandaar de ORDER BY in de subquery van de left join

SELECT
// enkele velden van de contactgegevens tabel​
FROM
contactpersonen AS c
LEFT JOIN contactgegevens as cg ON cg.contact_id = c.id
WHERE
cg.`status` != '9'
// enkele voorwaarden waaraan de contactgegevens moeten voldoen
ORDER BY previos_condition
GROUP BY
c.id



Probeer dit eens

Origineel gepost door Leander

SELECT
// enkele velden van de contactgegevens tabel​
FROM
contactpersonen AS c
LEFT JOIN contactgegevens as cg ON cg.contact_id = c.id
WHERE
cg.`status` != '9'
// enkele voorwaarden waaraan de contactgegevens moeten voldoen
ORDER BY previos_condition
GROUP BY
c.id


Probeer dit eens

Dat heb ik al geprobeerd, het probleem is alleen dat je dan niet de laatste rij van de contactgegevens krijgt. Of heb je hier de "previous_condition" voor toegevoegd? Dat is toch geen bestaande functie?

Alvast bedankt!

Dat is jou voorwaarde die je eerder had gesteld

Origineel gepost door Leander

Dat is jou voorwaarde die je eerder had gesteld

Ik ga het nog even testen, bedankt tot zover. Overigens ORDER BY en GROUP BY moeten omgekeerd voor mensen die jouw post als voorbeeld gebruiken.

Helaas heeft dit niet geholpen. Het is in MySQL niet mogelijk om te sorteren op de LEFT JOIN, daarvoor had ik die subquery gemaakt.

Origineel gepost door N P

Ik ga het nog even testen, bedankt tot zover. Overigens ORDER BY en GROUP BY moeten omgekeerd voor mensen die jouw post als voorbeeld gebruiken.

Succes en het kan dat ik iets verwisseld had. Vond het gewoon even leuk te doen.

Origineel gepost door N P

Helaas heeft dit niet geholpen. Het is in MySQL niet mogelijk om te sorteren op de LEFT JOIN, daarvoor had ik die subquery gemaakt.

Ik ben inmiddels wel een stap verder.

PHP Code:

(SELECT * FROM `contactgegevens` WHERE `status` != '9' ORDER BY `id` DESC) as `cg` 


Het sterretje in de subquery zorgt ervoor dat bij elke gevonden rij alle velden uit de tabel "contactgegevens" worden geselecteerd, terwijl ik er eigenlijk maar een aantal nodig heb. Standaard gebruik ik het sterretje dan ook niet, maar in dit geval wel omdat het in een voorbeeld stond over die subquery.

Nu het sterretje is vervangen door de benodigde velden, blijft er nog een laadtijd over van 0.9 sec.

Mocht iemand nog meer tips hebben dan hoor ik het graag. (Geen idee of hier echte SQL goeroes op dit forum zitten)

Heb je je indexes op orde?

De query is niet complex, maar gewoon van nature traag.

- in je subquery gebruik een descending order zonder limit (uiteraard), maar hierdoor moet wel de index voor elke contactpersoon helemaal opnieuw doorlopen worden.
- daardoor is er ook geen optimalisatie van je subquery mogelijk
- je gebruikt een != 9, dat is per definitie niet te optimaliseren

Hierdoor ben je dus 1070 * 1803 records aan het verwerken terwijl je er maar 1070 + 1070 nodig hebt.

Als je nu een veldje 'laatste' erbij zet en elke keer als je een contactgegeven toevoegt hier het veldje 'laatste' op 1 zet en bij het vorige van die persoon het 'laatste' veldje op 0 zet, dan hoef je alleen een de index op contactgegevens 'contact_id' uit te breiden met het 'laatste' veld om het zo snel mogelijk te hebben.

SELECT
// enkele velden van de contactgegevens tabel​
FROM
contactpersonen c, contactgegevens cg WHERE c.id = cg.contact_id and cg.laatste != '1'
and // andere voorwaarden waaraan de contactgegevens moeten voldoen

Origineel gepost door Mark Puts

Heb je je indexes op orde?

Ja, bedankt voor je reactie!

Origineel gepost door John Timmer

De query is niet complex, maar gewoon van nature traag.

- in je subquery gebruik een descending order zonder limit (uiteraard), maar hierdoor moet wel de index voor elke contactpersoon helemaal opnieuw doorlopen worden.
- daardoor is er ook geen optimalisatie van je subquery mogelijk
- je gebruikt een != 9, dat is per definitie niet te optimaliseren

Hierdoor ben je dus 1070 * 1803 records aan het verwerken terwijl je er maar 1070 + 1070 nodig hebt.

Als je nu een veldje 'laatste' erbij zet en elke keer als je een contactgegeven toevoegt hier het veldje 'laatste' op 1 zet en bij het vorige van die persoon het 'laatste' veldje op 0 zet, dan hoef je alleen een de index op contactgegevens 'contact_id' uit te breiden met het 'laatste' veld om het zo snel mogelijk te hebben.

SELECT
// enkele velden van de contactgegevens tabel​
FROM
contactpersonen c, contactgegevens cg WHERE c.id = cg.contact_id and cg.laatste != '1'
and // andere voorwaarden waaraan de contactgegevens moeten voldoen

Bedankt voor je reactie John!
Dit is na de voorgaande wijzigingen nog precies waar de schoen wringt. Ik gebruikte status != 9 om de laatste te pakken, nu blijkt dat dit niet de juiste status is waardoor de query inderdaad alsnog langs alle records ging.

Een nieuwe test met de juiste status geeft een snelheid van 0.6 sec.
Zou het nog sneller kunnen?


Edit: het zoeken bij de WHERE werkt natuurlijk ook niet meer op de oude gegevens, ik ga nog even bedenken of dit nodig is.

Edit 2: Eerder genoemde snelheden waren met SQL_NO_CACHE, om de daadwerkelijke snelheid te zien.

Zoals Mark zei, welke indexes heb je ingesteld? Dat is meestal iets waar je erg veel winst kunt krijgen.

Origineel gepost door N P

Ja, bedankt voor je reactie!



Bedankt voor je reactie John!
Dit is na de voorgaande wijzigingen nog precies waar de schoen wringt. Ik gebruikte status != 9 om de laatste te pakken, nu blijkt dat dit niet de juiste status is waardoor de query inderdaad alsnog langs alle records ging.

Een nieuwe test met de juiste status geeft een snelheid van 0.6 sec.
Zou het nog sneller kunnen?


Edit: het zoeken bij de WHERE werkt natuurlijk ook niet meer op de oude gegevens, ik ga nog even bedenken of dit nodig is.

Edit 2: Eerder genoemde snelheden waren met SQL_NO_CACHE, om de daadwerkelijke snelheid te zien.

Het gebruik van '!=' of not of elke andere vorm van 'ongelijk aan' zorgt ervoor dat mysql veel meer records moet doorlopen via de index en niet kan optimaliseren. Mysqld begint zijn optimaliseren bij een inschatting welke index ie het beste kan gebruiken en dat ziet ie o.a. aan de cardinaliteit van de index waarin het 'where' veld voorkomt. Echter zie je daaraan alleen wat gelijk is, en niet wat ongelijk is.

Zet maar eens 'explain' of 'explain extended' voor je select als je hem in phpmyadmin uitvoert.
Dat helpt vaak wel om de juiste indexen te maken/kiezen of bij het herschrijven van je query.

In mijn voorbeeldquery moest trouwens 'laatste = 1' in de query staan, niet '!= 1'... :)