MYSQL datum in where

[toost]

Ik heb een vraag:

MySQL client version: 4.0.24

Ik heb een simpele query die ik wil optimaliseren. De Query in eerste instantie is als volgt:

code:

SELECT value AS total
FROM data
WHERE fieldid = 1
AND serviceid = 1
AND siteid = 8
AND date = '2007-02-03'

Deze query gaat er +- in 0.2s er door heen (zijn 65,076 rows in dbase)

date is een datefield en een datefield als een string behandelen is niet netjes.

Nu heb ik het ook als volgt geprobeerd:

code:

SELECT value AS total
FROM data
WHERE fieldid = 1
AND serviceid = 1
AND siteid = 8
AND YEAR(date) = 2007
AND MONTH(date) = 02
AND DAYOFMONTH(date) = 03

Dit is volgens mij netter, maar in snelheid maakt het niet veel uit. Het blijft rond de 0.2s.

Aangezien deze query een keer of 20 achter elkaar gedraaid wordt (PHP), Zit in een foreach en daar kan ik wat dat betreft weinig aan doen, is deze query een grote vertragende factor.

Nu is mijn vraag of er iemand een idee heeft over het optimaliseren van een query met een datum.

Ik moet ook nog zeggen dat ik het volgende heb geprobeerd:

code:

SELECT value AS total
FROM data
WHERE fieldid =1
AND serviceid =1
AND siteid =8
AND DATE_ADD(date, interval 1 DAY) = '2007-02-03'

Alleen geeft dit de verkeerde resultaten (hij pakt hier als datum 2007-02-01) Dus hier doe ik wat verkeerd. Maar een kleine note hierbij is dat ook deze query er zo'n 0.2s overdoet.

Ik heb een multi index gezet op fieldid,serviceid,siteid,datum dus dat zit volgens mij ook wel goed.

anyone?

[Macros]

Als je data aardig uniek zijn hoef je alleen maar een index aan te maken op je date veld. Ook kan je beter je foreach veranderen zodat hij alle queries in 1 grote query verpakt. De netwerk latency is namelijk ook substantieel, en als je ipv 20 1 query kan doen ben je al heel veel sneller.

[toost]

Macros schreef op dinsdag 06 februari 2007 @ 11:32:
Als je data aardig uniek zijn hoef je alleen maar een index aan te maken op je date veld. Ook kan je beter je foreach veranderen zodat hij alle queries in 1 grote query verpakt. De netwerk latency is namelijk ook substantieel, en als je ipv 20 1 query kan doen ben je al heel veel sneller.

Aan beide heb ik ook gedacht, de eerste gaat niet op omdat er op deze tabel ook andere queries draaien die de datum niet gebruiken, dus als ik alleen een index op het date veld maak worden die query's weer traag. En zoals ik al zei kan ik aan die foreach niks doen. Ik had zelf ook al het idee om 1 grote query te maken en deze te gebruiken maar het zit allemaal te genesteld in de code en heb niet genoeg tijd om het script aan te passen. Maar bedankt voor je input!

[gorgi_19]

Ik gok verder dat fieldId, ServiceId en SiteId de boel al zodanig filteren dat de index op date sowieso al weinig invloed meer zal hebben. Dat zal ws ook verklaren waarom de verschillen marginaal zijn.

[ Voor 18% gewijzigd door gorgi_19 op 06-02-2007 11:50 ]

[Voutloos]

toost schreef op dinsdag 06 februari 2007 @ 11:46:
de eerste gaat niet op omdat er op deze tabel ook andere queries draaien die de datum niet gebruiken, dus als ik alleen een index op het date veld maak worden die query's weer traag.

Je kan wel meer dan 1 index per tabel hebben hoor. Bekijk sowieso ook de EXPLAIN van die query. De query lijkt vrij triviaal, maar je zou toch maar net zien dat er iets doms gebeurd onder de motorkap.

[ripexx]

Voutloos schreef op dinsdag 06 februari 2007 @ 12:42:
[...]
Je kan wel meer dan 1 index per tabel hebben hoor. Bekijk sowieso ook de EXPLAIN van die query. De query lijkt vrij triviaal, maar je zou toch maar net zien dat er iets doms gebeurd onder de motorkap.

Maar MySQL gebruik er maar een van. Dus al heb je drie of vier indexen dan nog kan het traag zijn. In Indergeval moet je EXPLAIN gebruiken. Kijk hoe de tabel wordt benaderd en baseer daarop je queries. Het kan dus zijn dat je voor een bepaalde optimalisatie een samengestelde sleutel moet aanmaken. Maar op zich zouden dit soort queries in MySQL retesnel moeten zijn.

[Voutloos]

Lezen, ik zeg ook nergens dat er meer indexen gebruikt hoeven te worden. Je wil alleen voor deze query de beste index vinden en dat is niet per se fieldid,serviceid,siteid,datum (ivm bijvoorbeeld kardinaliteit).

[toost]

Ik heb het er gelukkig door dat ik de code mag aanpassen, en dat heb ik ondertussen al gedaan, scheeld een hoop tijd. Maar mijn vraag blijft staan om mijn query te optimaliseren. Wat is de netste en meest geoptimaliseerde methode om op een datum te vergelijken.

Verder zal ik eens naar EXPLAIN kijken, ik had er nog niet eerder van gehoord, ben benieuwd wat ik te zien krijg. Thnx voor de tip

Ik heb ondertussen naar EXPLAIN gekeken, en de index terug gezet naar 4 losse indexxen. Toen zag ik dat hij inderdaad alleen siteid, fieldid en serviceid gebruikt. Hierna de index weer op een multi index gezet en nu is er een kleine tijdswinst geboekt ipv 0.2s gaat hij nu in 0.17s. Weer wat geleerd

[ Voor 28% gewijzigd door toost op 06-02-2007 13:44 ]

[bigbeng]

Laat ik beginnen met een korte quote:

Premature optimization is the root of all evil

Maar in dit geval kan het handig zijn als je het volgende weet:
Bij de meeste databases (en ik geloof ook in MySQL) is het native datum type een long integer en daarmee kun je dus efficienter vergelijken als de waarde waarmee vergeleken wordt ook een datum is. Er van uitgaande dat MySQL zelf geen optimalisaties uitvoert, kun je het beste de waarde waarmee je vergelijkt omzetten in een datum. Want dan hoeven er zeker geen stringvergelijkingen plaats te vinden (wat op CPU niveau simpelweg langzamer is dan twee longs vergelijken).

SQL:

SELECT value AS total
FROM data
WHERE fieldid = 1
AND serviceid = 1
AND siteid = 8
AND date = STR_TO_DATE('2007-02-03')

Een beetje DBMS zal (vermoed ik) deze optimalisatie zelf al uitvoeren bij het opstellen van het execution plan, dus zie mijn quote ter referentie

Andere quote:

If it ain't broke, don't fix it!

[toost]

bigbeng schreef op dinsdag 06 februari 2007 @ 15:24:

SQL:

SELECT value AS total
FROM data
WHERE fieldid = 1
AND serviceid = 1
AND siteid = 8
AND date = STR_TO_DATE('2007-02-03')

Helaas is deze functie pas vanaf mysql 4.1.1 beschikbaar Dus ik kan het niet testen. Maar thnx voor de tip deze ga ik ook onthouden

[_js_]

toost schreef op dinsdag 06 februari 2007 @ 13:36:
Maar mijn vraag blijft staan om mijn query te optimaliseren. Wat is de netste en meest geoptimaliseerde methode om op een datum te vergelijken.

De datum met een enkele datum vergelijken, en geen functies op het veld gebruiken. Dus zo als je het had in de eerste query: WHERE datum = '2007-02-03'.

Functies moeten voor elke rij in de database ge-evalueerd worden, dat betekent dat elke rij in de database gelezen moet worden, dus WHERE YEAR(datum) = 2007 is niet goed.

[justmental]

Kun je geen bind variabelen gebruiken?

[ripexx]

Voutloos schreef op dinsdag 06 februari 2007 @ 13:16:
Lezen, ik zeg ook nergens dat er meer indexen gebruikt hoeven te worden. Je wil alleen voor deze query de beste index vinden en dat is niet per se fieldid,serviceid,siteid,datum (ivm bijvoorbeeld kardinaliteit).

Rustig maar Iig is het in versie 3 zo dat er maar één index per query wordt gebruikt. Maar voel je niet persoonlijk aangesproken was absoluut niet zo bedoeld.