[MySql] Zoeken in een tabel met ranges

[seweso]

Hoe kan ik slim querien om alle afspraken op te vragen die op een bepaalde dag plaatsvinden, waarbij sommige afspraken over meer dagen verspreid zijn door middel van een start en eind-datum.

Ik heb al geprobeerd een oplossing te vinden, maar de oplossingen die ik vind gaan over afspraken zoeken binnen een bepaalde range. Bij mij is het dus juist andersom: de afspraken bestaan uit ranges en de input is één datum.

Nu heb ik de volgende test tabel:

code:

CREATE TABLE `daterange` (
  `eventnaam` varchar(25) NOT NULL default '',
  `start` int(11) NOT NULL default '0',
  `einde` int(11) NOT NULL default '0',
  KEY `start` (`start`),
  KEY `einde` (`einde`),
  KEY `search` (`start`,`einde`)
) TYPE=MyISAM;

Die ik vul met de volgende door excel gegenereerde test-data (40000 records is een leuk aantal):

code:

A2 = KIEZEN(AFRONDEN(ASELECT()^8*5;0)+1;"afspraak"; "terugbellen"; "offerte"; "vergadering"; "vakantie"; "tandarts")
B2 = AFRONDEN(ASELECT()*50000;0)
C2 = B2+AFRONDEN(ASELECT() ^16 * 20;0)
D2 = "INSERT INTO `daterange` VALUES ('" & A2 & "', '" & B2 & "', '" & C2 & "');"

Ik gebruik nu de volgende query, maar die kan volgens mij slimmer:

code:

SELECT *
FROM `daterange` 
WHERE `start` <= '$dag' AND `einde` >= '$dag';

Met slimmer bedoel ik ook sneller, dus in plaats van "Query duurde 0.0553 sec" wil ik "Query duurde 0.0003 sec" zien

De optimalisatie waar ik zelf aan zat te denken heeft ermee te maken dat er gemiddeld maar een paar resultaten zijn voor één dag en dat in 99% van de gevallen de start-datum gelijk is aan de eind-datum. Daardoor zou in 99% van de gevallen een simpele "WHERE `start` = '$dag'" voldoende zijn en hoeft de complexere query op maar een klein gedeelte van het bestand uitgevoerd worden ... dus --> snelheidswinst

Dus dacht ik ik leg gewoon een index op de expressie "einde - start" maar dat ondersteund MySql niet...

Voor nu geef ik gebruikers gewoon niet de mogelijkheid om taken over meer dagen te laten lopen, maar het zou cooler zijn als ik die mogelijkheid wel kon bieden.

Er is natuurlijk ook een tussenvorm mogelijk waarbij ik de mogelijkheid geef om een taak maar over een beperkt aantal dagen te spreiden waardoor volgens mij de ... WAUW dat helpt super (0.0004 sec). Maar ja als iemand nog een betere oplossing weet

[Sjaaky]

Misschien dat een aparte index op start en op einde helpt.

Maar je zou natuurlijk een tabel met dagen kunnen maken met verwijzingen naar de afspraken op die dag. Met een index op de datum in die tabel kan je erg snel zoeken. Welke methode uiteindelijk beter is hangt af van de verhouding tussen het aantal keer dat je een afspraak toevoegt/bijwerkt en het aantal keer dat je een afspraak opvraagt.

[seweso]

Sjaaky schreef op 13 april 2004 @ 17:08:
Misschien dat een aparte index op start en op einde helpt.

Maar je zou natuurlijk een tabel met dagen kunnen maken met verwijzingen naar de afspraken op die dag. Met een index op de datum in die tabel kan je erg snel zoeken. Welke methode uiteindelijk beter is hangt af van de verhouding tussen het aantal keer dat je een afspraak toevoegt/bijwerkt en het aantal keer dat je een afspraak opvraagt.

Dus als ik een taak toevoeg die een maand duurt dan moet ik 30 records aanmaken? Maar ja de query snelheid zou dat inderdaad de snelste manier zijn.

[dusty]

$dag between begin and einde

[seweso]

dusty schreef op 13 april 2004 @ 19:26:
$dag between begin and einde

Het leest misschien lekkerder maar sneller is het niet...

[ACM]

en dit?

code:

SELECT *
FROM `daterange` 
WHERE
( start = '$dag' AND start = einde )
OR
( start <= '$dag' AND `einde` >= '$dag' )

Of als je mysql 4+ gebruikt:

code:

SELECT *
FROM `daterange` 
WHERE
 start = '$dag' AND start = einde
UNION
SELECT *
FROM `daterange` 
WHERE
 start <= '$dag' AND `einde` >= '$dag'

[ Voor 9% gewijzigd door ACM op 14-04-2004 11:39 ]

[seweso]

ACM schreef op 14 april 2004 @ 11:38:
en dit?

code:

SELECT *
FROM `daterange` 
WHERE
( start = '$dag' AND start = einde )
OR
( start <= '$dag' AND `einde` >= '$dag' )

Of als je mysql 4+ gebruikt:

code:

SELECT *
FROM `daterange` 
WHERE
 start = '$dag' AND start = einde
UNION
SELECT *
FROM `daterange` 
WHERE
 start <= '$dag' AND `einde` >= '$dag'

Dat is toch overduidelijk langzamer (ik heb het voor de zekerheid gechecked).

[ACM]

seweso schreef op 14 april 2004 @ 15:10:
Dat is toch overduidelijk langzamer (ik heb het voor de zekerheid gechecked).

Ik heb ze beiden niet gechecked, maar zou ze niet suggereren als ik dacht dat ze overduidelijk langzamer zouden zijn. Ze zijn trouwens ook niet helemaal correct en dat zal in dit geval aardig meespelen.

De start <= $dag moet start < $dag zijn.

[cameodski]

ACM schreef op 14 april 2004 @ 16:44:
[...]

Ik heb ze beiden niet gechecked, maar zou ze niet suggereren als ik dacht dat ze overduidelijk langzamer zouden zijn. Ze zijn trouwens ook niet helemaal correct en dat zal in dit geval aardig meespelen.

De start <= $dag moet start < $dag zijn.

Ik denk toch dat < of <= weinig verschil maakt in dit geval. Sterker: ik denk dat de optimizer voldoende intelligent is om dit nagenoeg even snel uit te kunnen voeren.

Er staat mij vaag bij dat ik wel eens ergens gelezen heb, dat als er sprake is van een samengestelde index, dat er dan op de eerste kolom in de index wel snel gezocht kan worden, maar dat dat bij de tweede kolom niet kan.
Het gevolg zou dan zijn, dat er heel veel records voldoen aan de filter op 'start' en dat het databeest vervolgens de hele index moet scannen om de filter op 'einde' uit te voeren.

Eventueel zou je een kolom kunnen toevoegen waarin je einde - start opslaat, maar de vraag is of het bijwerken van deze kolom niet zoveel tijd kost, dat je uiteindelijk nog beroerder af bent.

[ACM]

Een gecombineerde index op start-einde kan idd nut hebben, zeker als er steeds range searches als deze gedaan worden. De verandering van <= in < heeft zeker voor de union-versie nut, althans als er vooral veel records aan het eerste vereiste voldoen (dus date = $dag).

[ Voor 4% gewijzigd door ACM op 14-04-2004 20:01 ]