[mysql] selectie datum range optimaliseren

[BetuweKees]

hi,

heb een database draaien met daarin de verwijzingen naar een collectie foto's. gesorteerd op datum, zodat in een website met een knopje vorige/volgende doorgelinkt kan worden naar de opvolgende foto's in de zelfde reeks.

momenteel zoek ik deze volgende en vorige foto op middels twee queries;

code:

SELECT foto WHERE datum > foto_datum LIMIT 1;

en vise versa.

aangezien de collectie inmiddels behoorlijk groot geworden is, gaat deze query steeds langzamer; op dit moment zo rond de 6 seconden voor beide queries samen. veel te langzaam natuurlijk, en nauwelijks meer werkzaam. hoog tijd voor een optimalisatie dus. ik vraag me echter af wat hier de beste manier voor is, en of ik allicht een andere query kan gebruiken om hetzelfde resultaat sneller te gebruiken.

het inkrimpen van het gebied waarbinnen gezocht kan worden (datum plus/min een week bv dmv DATEDIFF) helpt er goed, en geeft volgens mijn benchmarks een snelheidsverhoging van zeker tien keer (meer naarmate een datum meer naar de extremen gekozen wordt), het enige is dat de query nu niet meer 100% waterdicht is; verschijnt er voor langer dan een week geen foto in de collectie dan gaat het fout. een kleinere window zorgt voor een snellere query, een grotere voor een langzamere, maar het probleem blijft; gaat de tijd tussen twee foto's over een bepaalde grens heen, dan loopt het spaak.

zijn er allicht nog andere manieren (sneller of waterdichter) om dit probleem op te lossen?

[Megamind]

Zet er een index op, als je die nog niet hebt. Hoeveel records zitten er in dan dat ie al zo traag wordt??

[frickY]

Denk niet dat er een index op die column zit. Dan lijkt 6sec me onmogelijk voor zoiets simpels.

[BetuweKees]

hah, ik dacht nog te vermelden dat ik ook al een index op de datum had zitten, maar dat ben ik dus vergeten. excuus daarvoor. er zit dus al een index op datum. helaas.

in totaal tegen de 3000 records in de tabel, en mysql meent 'where', 'temporary' en 'filesort' nodig te hebben om alles netjes op een rijtje te zetten..

voor de liefhebber:

code:

SELECT
    foto.slide,
    DATE_FORMAT( foto.datum, '%Y/%m/%d' ) AS datum,
    omschrijving.titel,
    land.naam AS land,
    land.trackback
FROM
    foto AS foto
LEFT JOIN
    foto_omschrijving AS omschrijving ON
        omschrijving.foto_id = foto.foto_id AND omschrijving.taal_id = 1
LEFT JOIN
    land AS land ON
        foto.landen_id = land.landen_id AND land.taal_id = 1
WHERE 
    foto.datum > '2006-08-24 18:54:21'
    AND
    DATEDIFF(foto.datum, '2006-08-24 18:54:21') <= 1
ORDER BY
    foto.datum ASC
LIMIT 1;

[ Voor 47% gewijzigd door BetuweKees op 29-06-2007 18:56 ]

[ACM]

Hoe lang duurt enkel deze query, eventueel voorzien van die datediff?

SQL:

SELECT
    foto.id
FROM
    foto AS foto
WHERE 
    foto.datum > '2006-08-24 18:54:21'
ORDER BY
    foto.datum ASC
LIMIT 1

Als dit snel is, probeer dan het volgende:

SQL:

SELECT
    foto.slide,
    DATE_FORMAT( foto.datum, '%Y/%m/%d' ) AS datum,
    omschrijving.titel,
    land.naam AS land,
    land.trackback
FROM
    foto AS foto
LEFT JOIN
    foto_omschrijving AS omschrijving ON
        omschrijving.foto_id = foto.foto_id AND omschrijving.taal_id = 1
LEFT JOIN
    land AS land ON
        foto.landen_id = land.landen_id AND land.taal_id = 1
WHERE 
    foto.id = (SELECT
    foto.id
FROM
    foto AS foto
WHERE 
    foto.datum > '2006-08-24 18:54:21'
ORDER BY
    foto.datum ASC
LIMIT 1)

Ik neem verder maar aan dat je foto_omschrijving een index op foto_id (of taal_id, foto_id) heeft en dat je land-tabel een index op landen_id of (taal_id, landen_id) heeft? Mocht die subquery individueel wel heel snel zijn, maar de samenstelling niet, dan kan je ook nog overwegen om dat teruggegeven id los op te halen en er programmatisch in te verwerken.

[FragFrog]

Is die datum de datum waarop de foto is gemaakt of de datum waarop hij is ingevoegd?

In het laatste geval is het wellicht -veel- sneller om te zoeken op het eerstvolgende ID. Zou niet uit mogen maken tov een datumsearch met index, maar ik kan moeilijk geloven dat een query op een tabel met amper 3000 records 6 seconden moet duren als je wel een index gebruikt, wellicht het proberen waard

[BetuweKees]

ACM schreef op vrijdag 29 juni 2007 @ 19:32:
Hoe lang duurt enkel deze query, eventueel voorzien van die datediff?

-> 1 row in set (0.33 sec)

de subquery zorgt er voor dat de gehele pagina in ook ongeveer die tijd weergegeven wordt; vooruitgang dus! stond al bijna op het punt weer een teleurgesteld reaktie te geven over dat ik enkel mysql 3.23 kon draaien, maar dan zie ik zojuist toch dat mijn hoster eindelijk eens mysql 4.1 is gaan gebruiken. joepie!

bedankt voor je tip, en nog meer natuurlijk voor de daar vanuit voortvloeiende realisatie dat ik vanaf nu ook eens modern kan gaan doen!