[MySQL] Index op datum

[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?

[Ramon Fincken]

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

[VinceSTM]

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.

[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 ';

[VinceSTM]

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)

[systemdeveloper]

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.

[systemdeveloper]

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.

[VinceSTM]

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

[Ramon Fincken]

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

gaan we onthouden

[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

[systemdeveloper]

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.

[systemdeveloper]

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?)

[VinceSTM]

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'

[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?

[VinceSTM]

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