Onderwerp: [MySQL] Index op datum

Het volgende komt net op mijn pad:

tabel: bevat een aantal kolommen waaronder een datum

Query bevat: where `Datum` >= '01-01-2008' AND `Datum` <= '31-12-2008'

Index op datum, genaamd datum

explain ~ where `Datum` >= '01-01-2008' AND `Datum` <= '31-12-2008'

geeft:

id:1
select_type: SIMPLE
table: Factuur
type: ALL
possible_keys: datum
rows: 783
Extra: Using where

Dit betekent dat ie alle 783 rijen afgaat... dat is toch ook niet de bedoeling?

does een count? heb je ook echt 783 rijen in je DB staan of puur voor dit jaar?

Oorspronkelijk geplaatst door Ramon Fincken

does een count? heb je ook echt 783 rijen in je DB staan of puur voor dit jaar?

Is niet mijn database, maar er staan 783 rijen in het totaal in.

hmm die explain kende ik nog niet, je kan wellicht eens in de documentatie kijken naar de build in date functions, wellicht dat die beter ge-fine-tuned zijn

dan krijg je dingen als:
$sql .= 'AND DATE_ADD(CURDATE(),INTERVAL 2 MONTH) >= end_date AND CURDATE() <= end_date ';

Oorspronkelijk geplaatst door Ramon Fincken

hmm die explain kende ik nog niet, je kan wellicht eens in de documentatie kijken naar de build in date functions, wellicht dat die beter ge-fine-tuned zijn

dan krijg je dingen als:
$sql .= 'AND DATE_ADD(CURDATE(),INTERVAL 2 MONTH) >= end_date AND CURDATE() <= end_date ';

die geeft helaas geen betere resultaten (al geprobeerd)

Oorspronkelijk geplaatst door VinceSTM

Het volgende komt net op mijn pad:

tabel: bevat een aantal kolommen waaronder een datum

Query bevat: where `Datum` >= '01-01-2008' AND `Datum` <= '31-12-2008'

Index op datum, genaamd datum

explain ~ where `Datum` >= '01-01-2008' AND `Datum` <= '31-12-2008'

geeft:

id:1
select_type: SIMPLE
table: Factuur
type: ALL
possible_keys: datum
rows: 783
Extra: Using where

Dit betekent dat ie alle 783 rijen afgaat... dat is toch ook niet de bedoeling?

Ok, om dat duidelijk te krijgen moet je even iets over mysql weten.

1) zet je datums in normaal mysql formaat en maak een datetime veld van de datum
2) run regelmatig 'optimize table' als je veel insert hebt.
3) gebruik een BETWEEN (vanwege leesbaarheid)

Nou, aangenomen dat je dit al doet (op de BWTWEEN na dan), heb je te maken met een zogenaamde RANGE scan van mysql. Op basis van de cardinaliteit van een index op het datumveld en zijn optimaliserings algoritme 'schat' mysql het aantal rijen dat je query gaat opleveren. Zodra dit een bepaald percentage overschrijdt zal mysql de index niet gebruiken maar altijd een tabel scan doen.

Stel je hebt een index op een man/vrouw veld in een tabel met 100 rijen. Die kan dus 2 waarden aannemen en een query op dat veld zal gem. 50% van de rijen teruggeven. Met indexgebruik betekent dat 100 x een (logische) random access benadering van de tabel. Zonder index betekent het 100 records sequentieel lezen. Dat is veel sneller natuurlijk.

Om op je vraag teug te komen. Als je het interval tussen de 2 datum kleiner maakt (bv 1 dag) dan zul je met EXPLAIN zien dat de index opeens wel gebruikt wordt.

Oorspronkelijk geplaatst door Ramon Fincken

hmm die explain kende ik nog niet, je kan wellicht eens in de documentatie kijken naar de build in date functions, wellicht dat die beter ge-fine-tuned zijn

dan krijg je dingen als:
$sql .= 'AND DATE_ADD(CURDATE(),INTERVAL 2 MONTH) >= end_date AND CURDATE() <= end_date ';

Check 'EXPLAIN EXTENDED' en 'SHOW WARNINGS'. Ook wel eens handig bij optimatisering.

ik gebruik een date-veld (0000-00-00) van mysql, ik kan dus beter datetime gebruiken?

Oorspronkelijk geplaatst door systemdeveloper

Check 'EXPLAIN EXTENDED' en 'SHOW WARNINGS'. Ook wel eens handig bij optimatisering.

gaan we onthouden

Oorspronkelijk geplaatst door VinceSTM

ik gebruik een date-veld (0000-00-00) van mysql, ik kan dus beter datetime gebruiken?

Hij doet het met een interval van 1 dag ook niet overigens

Oorspronkelijk geplaatst door VinceSTM

ik gebruik een date-veld (0000-00-00) van mysql, ik kan dus beter datetime gebruiken?

Nee, datetime is ook goed. Ik ging er vanuit dat je geen date/datetime veld had vanwege je query met datums in dag-maand-jaar formaat (aka. stringformaat). Als het een dateveld is, des te beter want dat is korter en sneller.

Oorspronkelijk geplaatst door VinceSTM

Hij doet het met een interval van 1 dag ook niet overigens

Paste die hele query eens? (Zeker dat je datums als 'YYY-MM-DD' opgeeft?)

Oorspronkelijk geplaatst door systemdeveloper

Paste die hele query eens? (Zeker dat je datums als 'YYY-MM-DD' opgeeft?)

select `Factuur_ID`, `Deb _ID`, `Datum`, `Betalingstermijn`, `Bedrag`, `BTW`, `Totaal`, `BetalingsID` from `Factuur` where `Datum` between '01-01-2008' AND `Datum` <= '02-01-2008'

probeer nu gewoon dit eens, want die van jou zou een syntax error moeten geven als het 'goed' is

explain select `Factuur_ID`, `Deb _ID`, `Datum`, `Betalingstermijn`, `Bedrag`, `BTW`, `Totaal`, `BetalingsID` from `Factuur` where `Datum` between '2008-01-01' AND '2008-01-02'

Nog steeds geen index gebruik?

Oorspronkelijk geplaatst door systemdeveloper

probeer nu gewoon dit eens, want die van jou zou een syntax error moeten geven als het 'goed' is

explain select `Factuur_ID`, `Deb _ID`, `Datum`, `Betalingstermijn`, `Bedrag`, `BTW`, `Totaal`, `BetalingsID` from `Factuur` where `Datum` between '2008-01-01' AND '2008-01-02'

Nog steeds geen index gebruik?

nee, geen index