[SQL] gaten vinden in een getallen reeks is het mogelijk?

[Uhmmie]

Ik zit al een tijdje te pijnzen over het volgende vraagstuk.

Stel ik heb de volgende tabel:
van ; tot
1 ; 3
4 ; 5
5 ; 6
8 ; 10

Is het mogelijk om via SQL te vinden dat er gat van meer dan 1 zit tussen 6 en 8? Dat ook nog een soepel zou draaien bij een tabel met 100.000+ rijen? Het zou gevoelsmatig moeten lukken maar ik kom er maar niet op

[Onbekend]

Gebruik een tijdelijke tabel.
Vul deze eerst t/m maximum getal, en haal daarna de getalllen weg die uit je huidige tabel worden gedekt.

[Boss]

Met een join op [rechterkolom] <> [linkerkolom]+1 of zoiets?

[albino71]

Ik ga er even vanuit dat je twee kolommen hebt; "van" en "tot".

Als van je huidige record de waarde "tot" meer dan 1 verschilt van de waarde "van" van je prior record, dan wil je dat weten?

Met een cursor werken en current van en prior tot met elkaar vergelijken.
Zodra het verschil groter is, moet je nog beslissen welke van de twee de boosdoener is.
Is "tot" te klein, of "van" te groot.

[Uhmmie]

Misschien was mijn voorbeeld niet helemaal handig gekozen.
Stel dat ik ga zoeken van gaten groter dan 4.
Daarnaast kan ik overlap hebben tussen de diverse rijen.

Volgens mij gaan dan jullie ideeën helaas niet op

Nieuw voorbeeld:

1 ; 3
3 ; 10
6 ; 8
9 ; 15
20 ; 23

[ Voor 19% gewijzigd door Uhmmie op 06-06-2013 22:45 ]

[albino71]

Als je nu ook nog even aangeeft welk record je wil weten...

Ik neem aan 9;15 of 20;23 in je laatste voorbeeld?

[Ghehe]

Met procedurale SQL kan je zoiets wel (gemakkelijk) oplossen.

[Uhmmie]

albino71 schreef op donderdag 06 juni 2013 @ 22:45:
Als je nu ook nog even aangeeft welk record je wil weten...

Ik neem aan 9;15 of 20;23 in je laatste voorbeeld?

Ik zou in mijn voorbeeld eigenlijk de 20 willen weten, zodat ik de data kan splitsen vanaf dat punt.

En stel mijn voorbeeld was:
1 ; 3
3 ; 10
6 ; 8
9 ; 15
20 ; 23
24 ; 26
33 ; 35

Dan zou ik graag
20
33

Terug krijgen

[ Voor 17% gewijzigd door Uhmmie op 06-06-2013 22:49 ]

[frickY]

Met subqueries moet dit ook prima lukken.

Men neme een rij, en met een subquery de daarop volgende rij (where t2.min > t1.max order by t2.min limit 1), trekt t2.min af van t1.max, en je weet de tussenruimte. Met een HAVING-clause kun je de records filteren met een tussenruimte die je groot genoeg vind.

Maar het genereren van een temporary table kon wel eens een stuk sneller zijn, ookal hoeft bovenstaande met de juiste indexen geen probleem te zijn.

[ Voor 37% gewijzigd door frickY op 06-06-2013 22:49 ]

[albino71]

Welk DBMS hebben we het over btw?

[Uhmmie]

albino71 schreef op donderdag 06 juni 2013 @ 22:48:
Welk DBMS hebben we het over btw?

Maakt niet zoveel uit. Mysql of mssql of een degelijke database.

[pedorus]

Het idee lijkt mij zoiets:

SQL:

create table tabel(van int,tot int);    
insert into tabel values (1,3),(3,10),(6,8),(9,15),(20,23);
select distinct tot+1 from tabel where (not exists (select * from tabel as t2 where 
    (t2.van < tabel.tot + 1) and (t2.tot > tabel.tot)))
    and not tot = (select max(tot) from tabel);

Nu aan jouw om het aan te passen aan gaten van 4, en aan eindwaardes ipv beginwaardes, want je edit de opdracht steeds

[ Voor 8% gewijzigd door pedorus op 06-06-2013 22:55 . Reden: veranderende opdracht ]

[ThomasG]

Uhmmie schreef op donderdag 06 juni 2013 @ 22:51:
[...]

Maakt niet zoveel uit. Mysql of mssql of een degelijke database.

Dat maakt wel uit, omdat je per DBMS specifieke features hebt welke het wellicht kunnen versimpelen.

[Uhmmie]

frickY schreef op donderdag 06 juni 2013 @ 22:48:
Met subqueries moet dit ook prima lukken.

Men neme een rij, en met een subquery de daarop volgende rij (where t2.min > t1.max order by t2.min limit 1), trekt t2.min af van t1.max, en je weet de tussenruimte. Met een HAVING-clause kun je de records filteren met een tussenruimte die je groot genoeg vind.

Maar het genereren van een temporary table kon wel eens een stuk sneller zijn, ookal hoeft bovenstaande met de juiste indexen geen probleem te zijn.

Door overlapping gaat jou eerste oplossing helaas niet.

Ik zie niet hoe je dit met een tijdelijk tabel op lost of wil je daarna gaan tellen of er ergens nog 4 opvolgende cijfers zijn? Dat lijkt me zeer langzaam gaan.

[ Voor 66% gewijzigd door Uhmmie op 06-06-2013 23:04 ]

[Uhmmie]

pedorus schreef op donderdag 06 juni 2013 @ 22:51:
Het idee lijkt mij zoiets:

SQL:

create table tabel(van int,tot int);    
insert into tabel values (1,3),(3,10),(6,8),(9,15),(20,23);
select distinct tot+1 from tabel where (not exists (select * from tabel as t2 where 
    (t2.van < tabel.tot + 1) and (t2.tot > tabel.tot)))
    and not tot = (select max(tot) from tabel);

Nu aan jouw om het aan te passen aan gaten van 4

Die oplossing werkt volgens mij niet met gaten groter dan 1 helaas ik een degelijke oplossing al tegen gekomen maar ik heb het na een uur op gegeven.

En ik bedoelde dat voor mij de database niet uit maakt eenmaal de juiste richting dan kan ik het wel zelf verderop bouwen naar een andere rmdbs maar dat is op dit moment helemaal niet van belang.

[ Voor 13% gewijzigd door Uhmmie op 06-06-2013 22:57 ]

[Onbekend]

Ik begreep dat je een enkel nummer nodig had, maar als je er 4 van nodig hebt is het lastiger om ze straks uit de tijdelijke tabel te filteren. Maar in de tijdelijke tabel heb je je data al gefiltert.

Volgens mij gaat mijn oplossing bij grote hoeveelheden regels sneller dan met een complexe query.

[pedorus]

Toch heeft een oplossing met gaten van 4 maar een edit distance van 1 met de door mij gegeven oplossing. NB: mijn oplossing zoekt nu op gaten van 0 en groter, niet van 1 en groter. Wellicht even de tijd nemen en de boel eens rustig uittekenen?

Database maakt ook iets uit voor de performance van sommige oplossingen, bijvoorbeeld partition zou handig kunnen zijn hier.

[ Voor 9% gewijzigd door pedorus op 06-06-2013 23:18 . Reden: ++hints ]

[Uhmmie]

Ik ben er uit achter gekomen dat het lastiger was dan ik in eerste instantie had verwacht ik ga die artikelen eens lezen of ik daar iets mee kan

[P.O. Box]

volgens mij komt zoiets al aardig in de buurt (ipv kijken welke WEL opvolgen ga je kijken welke NIET in een reeks vallen):

SQL:

CREATE TABLE datatable (
  van int(11) default NULL,
  tot int(11) default NULL
)

INSERT INTO datatable VALUE (1 , 3), (3, 10), (6 , 8), (9, 15), (20 , 23), (24, 26), (33, 35);

select datatable.van
from
datatable
left join datatable as subdatatable
on datatable.van between subdatatable.van + 1 and subdatatable.tot + 1  
where subdatatable.van is null
;

je krijgt nu als resultaat: 1, 20, 33.... en dat is op zich logisch... die 1 kun je er op een andere manier nog wel uitfiteren als je die niet wilt hebben...

[ Voor 20% gewijzigd door P.O. Box op 06-06-2013 23:22 ]

[pedorus]

De Mysql-variant van mijn oplossing - http://stackoverflow.com/...s-left-join-where-is-null

[ Voor 13% gewijzigd door pedorus op 06-06-2013 23:31 ]

[Gomez12]

Waar komt de data vandaan? En op welke manier is deze oplopend (altijd of slechts per dag of ... of ...)? En moet het live gebeuren, of mag het bijv ook periodiek bijgewerkt worden?


In principe zou ik mits het live moet zijn een extra veld introduceren distance till next en dan met insert / update / delete triggers dat veld bijhouden.
Alhoewel ik in de praktijk waarschijnlijk het veld ook wel zou maken alleen dan periodiek iets draaien wat het vult (ik heb het niet zo op triggers die meerdere rows raken en bij elke wijziging uitgevoerd moeten worden, vooral je update kan leuk worden als je die niet goed opschrijft)

Je probleem is simpelweg voornamelijk not the right tool for the right job... SQL werkt set-based en niet row-based (wat jij wilt hebben)

[Patriot]

Die oplossing gaat sowieso niet werken, want sets kunnen overlappen.

EDIT: Misschien is het handig als de TS het uiteindelijke probleem dat hij wil oplossen geeft. Dan kunnen we misschien een ander voorstel doen.

[ Voor 52% gewijzigd door Patriot op 06-06-2013 23:51 ]

[ThomasG]

Uhmmie schreef op donderdag 06 juni 2013 @ 22:46:
[...]

Ik zou in mijn voorbeeld eigenlijk de 20 willen weten, zodat ik de data kan splitsen vanaf dat punt.

En stel mijn voorbeeld was:
1 ; 3
3 ; 10
6 ; 8
9 ; 15
20 ; 23
24 ; 26
33 ; 35

Dan zou ik graag
20
33

Terug krijgen

In PostgreSQL:

SQL:

SELECT van 
FROM   (SELECT van, 
               tot, 
               LEAD(tot) 
                 OVER( 
                   ORDER BY van DESC) AS lt 
        FROM   tabel) t 
WHERE  t.van - t.lt > 1

Geeft 33 en 20 terug. Alleen gaat het waarschijnlijk fout als n-2 wel overlapt.

[Uhmmie]

Het doel is dat ik data wil scheiden..
Ipv alleen een van en tot heb ik ook een id en een start_id.

ID	START_ID	VAN	TOT
1	1	1	3
2	1	5	10
3	1	9	20
4	1	12	13
5	1	15	18
6	1	23	40

In dit voorbeeld zit er nergens een gat groter dan 4.
Wat ik nu wil zien is dat als ik nu 1 of meerdere rijen verwijderd.
Of er dan ergens in de reeks een gat groter dan 4 zit.
Zoja dan moet de data gesplitst worden.

BV ik verwijder rij 2.

ID	START_ID	VAN	TOT
1	1	1	3
3	1	9	20
4	1	12	13
5	1	15	18
6	1	23	40

Nu wil ik dus vinden dat er tussen rij 1 en 3 een gat zit groter dan 4.
En dus de data afsplitsen door alles vanaf rij 3 aan rij 3 te koppelen:

ID	START_ID	VAN	TOT
1	1	1	3
3	3	9	20
4	3	12	13
5	3	15	18
6	3	23	40

Vervolgens update ik bv rij 4 dat deze niet begint met 12 maar vanaf 6 (t/m 13).

ID	START_ID	VAN	TOT
1	1	1	3
3	3	9	20
4	3	6	13
5	3	15	18
6	3	23	40

Nu wil ik dus weer opnieuw opzoek gaan naar de gaten groter dan 4 uur.
Ik zie in het bovenstaande geval dat die er niet meer zijn.
Waardoor ik alles weer terug wil hangen aan 1.

ID	START_ID	VAN	TOT
1	1	1	3
3	1	9	20
4	1	6	13
5	1	15	18
6	1	23	40

Ik hoop dat het een beetje duidelijk is wat ik wil bereiken.

Een nog mooiere oplossing zou zijn als ik de start_id zou kunnen generen in mijn query zodat ik deze niet tussen tijdig steeds uit hoeft te rekenen en op te slaan in mijn tabel.. Echter denk ik dat dat het probleem een stuk lastiger maakt .

Ik ga iig eens aan de slag om te zien of ik met jullie voorstellen echt niet tot een oplossing ga komen, want ik wil dit probleem gewoon eens proberen op te lossen .

[Gomez12]

Wat moet er uitkomen als ik na jouw laatste wijziging regel 3 verander van 9 tot 20 naar 9 tot 19?
Of wat moet er gebeuren als regel 4 veranderd van 6 tot 13 naar 6 tot 10 (over het totaal valt deze nog binnen 9 tot 20, maar tussen regel 4 en 5 heb ik toch een verschil >10)

Maar zeker met wat je er nu allemaal bij vertelt zou ik het opgeven om het in sql te doen (alhoewel het technisch vast wel kan), dit is gewoon compleet gebaseerd op row-by-row comparisons die je binnen een for-loopje en een if-combo (en een tellertje voor je id's) zo gemaakt hebt maar die in sql echt een ramp zijn (zeker het terugzetten van het id van 3 naar 1)


Of wellicht dat je je tabel anders kan herschrijven zodat het wel in set-based valt te proppen (ik zie even niet hoe maargoed)

[Uhmmie]

Ik heb het even van 19 naar 18 gemaakt zodat het gat groter is dan 4 (en niet 4).

ID	START_ID	VAN	TOT
1	1	1	3
3	1	9	18
4	1	6	13
5	1	15	18
6	6	23	40

Maar record 6 sluit dan dus niet meer aan bij de rest en komt op zich zelf te staan..

Natuurlijk kan ik het in php vrij makkelijke door heen loepen.. Alleen stel dat je op den duur 1.000.000 rows hebt.. Dan gaat het ook in php een enorme klus worden?

[Otherside1982]

Een leuke tip om dit alles uit te testen: http://sqlfiddle.com/#!2/3e0c2. Het schema heb ik al vlug even opgezet.
Ik zal straks misschien ook nog even kijken of ik hier iets meer mee kan.

[Gomez12]

Uhmmie schreef op vrijdag 07 juni 2013 @ 09:01:
Natuurlijk kan ik het in php vrij makkelijke door heen loepen.. Alleen stel dat je op den duur 1.000.000 rows hebt.. Dan gaat het ook in php een enorme klus worden?

A : SQL Server / Oracle kennen interne scripting mogelijkheden zodat je het wel binnen de db-server kan houden.
B : Daarom stel ik ook voor om intermediate results bij te houden in een extra kolom. Dan hoef je niet 1.000.000 rijen door, maar enkel de gewijzigde en alles wat daar rond ligt.

Je gevraagde functionaliteit (in je huidige opzet) vereist gewoon dat er door alle records heengeloopt wordt. Of herschrijf je functie-eisen of verander je db of je moet gewoon door alle records heenloopen.

[Woy]

Kan je niet gewoon iets als volgt doen?

SQL:

SELECT t1.van
FROM test t1
WHERE NOT EXISTS ( 
    SELECT * 
    FROM test t2 
    WHERE t2.van < t1.van
    AND t2.tot >= t1.van - 1 )

Ik weet niet hoe vaak de query uitgevoerd moet runnen, afhankelijk daarvan zou je inderdaad met gecachde gegevens kunnen werken.

[pedorus]

Laat ik anders het copy-paste werk doen en de oplossing voor gaten van 4 maar posten (geoptimaliseerd voor mysql):

SQL:

select tabel.id, tabel.van
from
tabel
left join tabel as subtabel
on tabel.van between subtabel.van + 1 and subtabel.tot + 4  
where subtabel.van is null
-- niet meer nodig voor het hoofdprobleem:
     and not tabel.van = (select min(van) from tabel);

Enkel hoe je dit resultaat gaat gebruiken om het hoofdprobleem op te lossen hangt sterk af van je database. In het geval van mysql ontbreken wat handigheidjes (updates met query op tabel zelf, group by in updates, cto/with of partition over) waardoor het lastig is om goede performance te krijgen. Het is in ieder geval een typisch groupwise maximum/groupwise minimum probleem als je het juiste id per rij wil vinden. Wat je in ieder geval niet zou moeten doen is een hack met order by (ongedefinieerd gedrag). (php doet me mysql vermoeden, maar een andere database zou dus handiger zijn)

Overigens is het waarschijnlijk handiger en duidelijker als je "van" gebruikt ipv "id" voor start. Verder is het gewoon een kwestie van testen met 1.000.000+ records, dan zie je vanzelf hoeveel tijd het kost..

[Uhmmie]

Iedereen bedankt.. Ik ben er zelf ook eindelijk uit gekomen:

code:

    SELECT data1.* from test_data data1
    LEFT JOIN test_data data2 ON (
        data1.test_id != data2.test_id AND
        data1.group_id = data2.group_id AND (
            ( data2.van < data1.van AND data2.tot > (data1.van-4))
        )
    )
    WHERE data1.group_id = 53420 and data2.test_id is null
    ORDER BY data1.van

Op deze manier heb ik zeg maar de rijen die direct na een gat zitten.
Vervolgens update ik alle rijen met alles wat tussen 'van' zit van de huidige rij en 'van' zit van de volgende rij, waarbij ik het start_id vul met het test_id van de geselecteerde rij en waarbij het start_id nog niet gelijk is aan dat id en dat lijkt redelijk goed te werken .

Bedankt voor alle tips en uitleg en ideeen . Kan ik vanavond ook weer lekker slapen tot ik weer een volgende idee heb

[ Voor 12% gewijzigd door Uhmmie op 07-06-2013 15:25 ]

[CaVeFiSh]

*nieuwe code beneden

[ Voor 98% gewijzigd door CaVeFiSh op 13-06-2013 17:45 ]

[Uhmmie]

Iemand hier met meer verstand van indexes dan mij?

code:

 SELECT t1.* from t t1
    LEFT JOIN t t2 ON (
        t1.id != t2.id AND
        t1.group_id = t2.group_id AND
        ( t2.van < t1.van AND t2.tot >= (t1.van-1440))
    )
    WHERE t1.group_id = 53420 and t2.id is null
    ORDER BY t1.van

Ik heb op de bovenstaande tabel een clustered primary key zitten op id, group_id, van, tot
Ik heb op de bovenstaande tabel nog een unique index zitten op id.
Ik heb op de bovenstaande tabel nog een normal index op group_id zitten.

Onder MSSQL werkt dit perfect. Alleen onder MySQL gaat het veel trager zodra er meer dan 1000 records onder dezelfde group_id vallen.

In alle gevallen hebben we 100.000+ records in de tabel t zitten.

Stel dat er nu binnen group_id 53420 10 records vallen dan duurt het in MSSQL iets van 0.2 seconde en in MySQL 0.1 seconde..

Maak ik van die 10 records 2000 records dan duurt het in MSSQL nog steeds iets van 0.2 seconde, maar in MySQL duurt het dan in eens 2 seconde.

In het begin had ik de primary key op id (en geen clustered index) en toen duurde hetzelfs 8 seconde.

Iemand nog enige tips hoe ik dit onder MySQL nog beter zou kunnen krijgen? Of is dit gewoon een limiet van MySQL? Maar waarom krijgt MSSQL het dan zoveel sneller voor elkaar?

[Woy]

Je moet eens kijken naar het execution plan, dan zie je welke indexen gebruikt worden, en hoe de query uitgevoerd worden.

In MSSQL kan dat in de management studio, en bij MySQL via het EXPLAIN commando.

[pedorus]

Uhmmie schreef op donderdag 13 juni 2013 @ 09:24:
Stel dat er nu binnen group_id 53420 10 records vallen dan duurt het in MSSQL iets van 0.2 seconde en in MySQL 0.1 seconde..

Maak ik van die 10 records 2000 records dan duurt het in MSSQL nog steeds iets van 0.2 seconde, maar in MySQL duurt het dan in eens 2 seconde.

Dit zijn toch aantallen van niks. Hoe kan het dat een berekening over 10 records, die de DB zo uit een index kan halen, 0.1 seconden duurt? (Of moet de data van disk komen van ~15 aparte locaties met trage schijven?)

Heeft de vergelijking t1.id != t2.id nut, of brengt dit de database in de war? Wat is het doel van deze query? Helpt het als je group_id 2 keer hard op 53420 zet?

Maak anders eens een gecombineerde index op (group_id, van, tot).

[Uhmmie]

pedorus schreef op donderdag 13 juni 2013 @ 10:58:
[...]

Dit zijn toch aantallen van niks. Hoe kan het dat een berekening over 10 records, die de DB zo uit een index kan halen, 0.1 seconden duurt? (Of moet de data van disk komen van ~15 aparte locaties met trage schijven?)

Heeft de vergelijking t1.id != t2.id nut, of brengt dit de database in de war? Wat is het doel van deze query? Helpt het als je group_id 2 keer hard op 53420 zet?

Maak anders eens een gecombineerde index op (group_id, van, tot).

Ik ben bang dat er tijdens mijn vorige meeting iets niet helemaal goed is gegaan.. Daarnaast moest ik nog een kleine wijziging door brengen:

code:

SELECT t1.* from t t1
    LEFT JOIN t t2 ON (
        t1.group_id = t2.group_id AND (
        ( t2.van < t1.van AND t2.tot >= (t1.van-1440)) OR
        ( t2.van = (t1.van-1440) AND t2.id < t1.id)
    ) 
    )
    WHERE t1.group_id = 53420 and t2.id is null
    ORDER BY t1.van

t1.id != t2.id brengt de database niet in de war maar was ook niet nodig, waar ik wel achter gekomen ben ik dat ik nog een extra or nodig had voor records met dezelfde 'van'.... Hierdoor zag ik ik ze beide niet.. nu pakt hij er 1 wat ik wil.

Het hardzetten van de t1.group_id = 53420 hielp qua preformance helaas niks.

Door het verhaal hierboven is de group_id, van, tot niet uniek (ik kan binnen dezelfde groep items hebben met dezelfde van en tot) en kan dus ook niet als primary key gezet worden.


Ik heb het vervolgens nogmaals getest:
Nieuwe waarde met 100.000 records in de tabel t duurt:
met 10 items in dezelfde group_id : MSSQL 0.2 en MySQL 0.00
met 2000 items in dezelfde group_id : MSSQL 1.3 en MySQL 2.68

Als ik nu de query herschrijf naar:

code:

SELECT t1.* from t t1
WHERE NOT EXISTS (
    SELECT * FROM t t2
    WHERE 
        t1.group_id = t2.group_id AND (
            ( t2.van < t1.van AND t2.tot >= (t1.van-1440)) OR
            ( t2.van = (t1.van-1440) AND t2.id < t1.id)
        ) 
) AND t1.group_id = 53420 
ORDER BY t1.van

met 10 items in dezelfde group_id : MSSQL 0.05 en MySQL 0.00
met 2000 items in dezelfde group_id : MSSQL 0.5 en MySQL 1.13

Weer iets beter maar nog steeds niet perfect..

[CaVeFiSh]

Uhmmie schreef op donderdag 13 juni 2013 @ 09:24:
Iemand hier met meer verstand van indexes dan mij?

code:

 SELECT t1.* from t t1
    LEFT JOIN t t2 ON (
        t1.id != t2.id AND
        t1.group_id = t2.group_id AND
        ( t2.van < t1.van AND t2.tot >= (t1.van-1440))
    )
    WHERE t1.group_id = 53420 and t2.id is null
    ORDER BY t1.van

Ik heb op de bovenstaande tabel een clustered primary key zitten op id, group_id, van, tot
Ik heb op de bovenstaande tabel nog een unique index zitten op id.
Ik heb op de bovenstaande tabel nog een normal index op group_id zitten.

Een index werkt als volgt: Je stelt 1 clustered index in op je primaire sleutel (Het record dat de tabel uniek maakt). Zoals ik het nu lees heb je meerdere kolommen in de index omsloten (surrogate) en dit gaat voor performance problemen zorgen. De rest van de kolommen waar je veelvoudig op gaat filteren daar maak je non-clustered indexes voor. In de clustered index wordt een fysieke ordening van de rows gehanteerd. In de non-clustered index worden weer de referenties gelegd naar de positie in de clustererd index. In jouw geval zou het er dus zo uit kunnen zien:

- clustered index op id (primaire sleutel)
- non-clustered index op group_id,van,tot

In dit geval neem ik de van en tot mee in de index (wat je normaal bijna nooit zal doen) omdat je left join afhankelijk is van deze kolommen. Het verschil tussen MSSQL en MySQL kan verklaard worden doordat in MSSQL gebruik wordt gemaakt van wat geavanceerdere execution plans.

[pedorus]

Het lijkt me sowieso logisch om t1.group_id=53420 als eerste neer te zetten. Verder bedoelde ik een index op (group_id,van,tot), niet zozeer een constraint of clustered index, zie hierboven.

(In theorie zou je als clustered index (group_id,van,id,tot) kunnen nemen trouwens, maar dat lijkt me vervelend met inserts/updates.)

Eventueel kun je anders ergens wat representatieve testdata posten, of in ieder geval de output van explain extended.

[CaVeFiSh]

pedorus schreef op donderdag 13 juni 2013 @ 13:31:
Het lijkt me sowieso logisch om t1.group_id=53420 als eerste neer te zetten.

En dit idd. Wat wellicht al een hele boel zal schelen is door de instructie volgorde van SQL eens onder de loep te nemen. De schijfvolgorde is dit: SELECT,FROM,WHERE,GROUP BY,HAVING,ORDER BY. Echter is dit niet de volgorde waarin SQL zijn instructies verwerkt, dat is namelijk dit: FROM,WHERE,GROUP BY,HAVING,SELECT,ORDER BY. Dat is ook de reden dat je bijvoorbeeld geen alias die je definieert in de select statement kan gebruiken in je GROUP BY maar wel in je ORDER BY (SELECT kom na GROUP maar voor ORDER in de instructievolgorde)

Wat ik hiermee duidelijk wil maken is het volgende:

In jouw query wordt er in de WHERE aangegeven dat je alleen group_id 53420 wilt ontsluiten. De LEFT JOIN behoord tot de FROM verwerking qua volgorde en wordt dus toegepast op elke record, daarna wordt pas de filtering toegepast. Een manier waarop je hiermee kan testen is door bijvoorbeeld De query om te schrijven zoals dit:

code:

SELECT T1.* FROM (SELECT t0.* from t t0 WHERE t0.group_id = 53420 ) T1
    LEFT JOIN t t2 ON (
        t1.id != t2.id AND
        t1.group_id = t2.group_id AND
        ( t2.van < t1.van AND t2.tot >= (t1.van-1440))
    )
    WHERE t2.id is null
    ORDER BY t1.van

*dit is ff uit mijn hoofd dus wellicht heb ik een foutje gemaakt. Ook zou een nettere oplossing misschien iets zijn met CTE's maar dit geeft denk ik duidelijker beeld.

[Uhmmie]

Thanks daar had ik wat aan.. MSSQL preformance is ongeveer gelijk gebleven..MySQL preformance is nu 0.4 seconde .. Uit eindelijke oplossing:

code:

SELECT t1.* FROM (SELECT t3.van, t3.tot, t3.id FROM t t3 WHERE t3.group_id = 300) t1
WHERE NOT EXISTS (
    SELECT * FROM (SELECT t4.van, t4.tot, t4.id FROM t t4 WHERE t4.group_id = 300) t2
        WHERE ( 
            ( t2.van < t1.van AND t2.tot >= (t1.van-1440)) OR
            ( t2.van = t1.van AND t2.id > t1.id)
        )
        ) 
ORDER BY t1.van

Kortom geen joins maar 2x dat truukje toepassen en dan een NOT EXISTS was veruit het snelste.. als ik dit idee deed met de left join was ik als nog 0.2 tot 0.3 trager .

[pedorus]

0.4 seconden is nog steeds erg lang voor een databasequery en doet ongeoptimaliseerd aan.

[Uhmmie]

Het valt best mee want ik store het resultaat hiervan weer in t, dus ik hoef het bij wijzigingen 1x uit te rekenen.. daarna kan ik uit het extra veld zo zien wat het start_id en kan ik zo alles met een simpele select selecteren .