[PHP/MYSQL] query-hoofdbrekertje

[Verwijderd]

Hoi! ik heb een table met 40 miljoen rijen waarvan op basis van onderstaande voorwaarden zeker de helft verwijderd kan worden alvorens ik een andere tactiek moet bedenken om in de toekomst het aantal onnodige records in deze tabel te verminderen..

Ik ga eerst maar eens mijn best doen om het in gewone taal uit te leggen, en hoor dan graag of iemand een idee heeft hoe dit query-hoofdbrekertje daadwerkelijk uitgevoerd te krijgen.

1. ik heb een table die er als volgt uit ziet:
2. ik wil alle rijen verwijderen waarvan, binnen dezelfde 'PiD', geen wijziging heeft plaatsgevonden in 'Col1' en 'Col2' ten opzichte van de eerstvolgende rij op basis van de 'Datum' ervoor, met als uitzondering de oudste datum en de nieuwste datum per PiD. een klein voorbeeld:

Id	PiD	Col1	Col2	Datum
1	120	23	44	2009-03-01	<- bewaren, er zijn geen rijen met een latere datum van PiD 120 + Col1 is gewijzigd
2	120	22	44	2009-02-18	<- bewaren, er zijn geen rijen met een vroegere datum van PiD 120
3	140	143	196	2009-03-01	<- bewaren, er zijn geen rijen met een latere datum van PiD 140
4	140	143	196	2009-02-26	<- verwijderen, col1 en col2 zijn hetzelfde als de rij ervoor (qua datum) van PiD 140
5	140	143	196	2009-02-23	<- verwijderen, col1 en col2 zijn hetzelfde als de rij ervoor (qua datum) van PiD 140
6	140	143	196	2009-02-12	<- bewaren, col2 is gewijzigd t.o.v. col2 in de rij ervoor (qua datum) van PiD 140
7	140	143	209	2009-02-05	<- bewaren, col1+col2 zijn gewijzigd t.o.v. de rij ervoor (qua datum) van PiD 140
8	140	144	199	2009-01-28	<- verwijderen, col1 + col2 zijn hetzelfde als in de rij ervoor (qua datum) van PiD 140
9	140	144	199	2009-01-25	<- verwijderen, col1 + col2 zijn hetzelfde als in de rij ervoor (qua datum) van PiD 140
10	140	144	199	2009-01-20	<- bewaren, er zijn geen rijen met een vroegere datum van PiD 140

Mijn hoofd raakt verward bij dit soort ingewikkelde wensen van mezelf, misschien kan iemand me (op weg) helpen hoe dit te bewerkstelligen, als het al onder deze voorwaarden te doen is. iets als... maar dan.. eeh.. anders

PHP:

$sql = mysql_query("SELECT DISTINCT (PiD) FROM tblname");

while ($row = mysql_fetch_array($sql)) 
{
$sql2 = "DELETE FROM tblname WHERE PRODUCT_ID = $row['PiD'] AND ((Col1 AND Col2 [i]both are the same compared to the row with the closest date before this row and has the same PiD[/i]) AND (Datum != MIN(Datum) AND Datum != MAX(Datum) [i]within same PiD[/i]))
}

Mijn dank gaat reeds uit naar diegene die alleen al de moeite hebben genomen te begrijpen wat hierboven staat

[NMe]

SQL:

SELECT Pid, Col1, Col2, MAX(Datum) AS Datum
FROM tabelnaam
GROUP BY Pid, Col1, Col2

Die query geeft je in elk geval alle records terug die je wil houden. Als je daar een beetje creatief een query omheen bouwt kun je vast ook wel verwijderen wat niet in die resultset staat. Ik ben momenteel een beetje te moe om er verder over na te denken.

edit:

Ik zie net je uitzondering, en die maakt het verhaal wel meteen een heel stuk complexer.

[ Voor 13% gewijzigd door NMe op 24-04-2009 05:25 ]

[Guillome]

Dit wil je in iedergeval niet doen met 1 query.

[Woy]

SQL:

DELETE FROM tabelnaam t1
WHERE not t1.Datum = (  
                SELECT MAX( t2.Datum )
                FROM tabelnaam t2 
                WHERE t2.Pid = t1.Pid
                    AND t2.Col1 = t1.Col1
                    AND t2.Col2 = t2.Col2 )

Zoiets misschien?

[ Voor 12% gewijzigd door Woy op 24-04-2009 10:56 ]

[Guillome]

moet dan ook MIN (t2.Datum) bij

[Woy]

Guillome schreef op vrijdag 24 april 2009 @ 10:55:
moet dan ook MIN (t2.Datum) bij

Ik denk dat ik nog niet helemaal wakker ben, maar ik snap nog niet precies wat de TS wil.

SQL:

DELETE FROM tabelnaam t1
WHERE not t1.Datum = (  
                SELECT MAX( t2.Datum )
                FROM tabelnaam t2 
                WHERE t2.Pid = t1.Pid
                    AND t2.Col1 = t1.Col1
                    AND t2.Col2 = t2.Col2 )
  AND not t1.Datum = (  
                SELECT MIN( t3.Datum )
                FROM tabelnaam t3 
                WHERE t3.Pid = t1.Pid
                    AND t3.Col1 = t1.Col1
                    AND t3.Col2 = t2.Col2 )

Zoiets dan? Zo behoud je binnen een groep ( Pid, Col1 en Col2 ) de eerste en de laatste datum

[ Voor 5% gewijzigd door Woy op 24-04-2009 11:00 ]

[DamadmOO]

Hetgeen wat de TS wil is het verwijderen van een rij wanneer de opvolgende rij van een bepaald PiD dezelfde informatie bevat in Col1 en Col2
[code=sql]
DELETE
FROM tabelnaam t1
WHERE EXISTS (
SELECT 1
FROM tabelnaam t2
WHERE t1.Pid = t2.Pid
AND t1.Col1 = t2.Col1
AND t1.Col2 = t2.Col2
AND t2.Datum = (
SELECT TOP 1 t3.Datum
FROM tabelnaam t3
WHERE t3.Datum > t1.Datum
AND t3.Pid = t1.Pid
ORDER BY t3.datum asc
)
)
[/code=sql]

En had het dus verkeerd gelezen.

[ Voor 85% gewijzigd door DamadmOO op 24-04-2009 11:08 ]

[LuCarD]

SQL:

SELECT t1.*
  FROM tablename AS t1
  INNER JOIN (
        SELECT `pid` , `col1` , `col2` , max( datum ) AS maxDate, min( datum ) AS minDate
        FROM tablename
        GROUP BY `pid` , `col1` , `col2`
        )  t2 
ON         t1.pid = t2.pid
      AND t2.col1 = t1.col1
      AND t2.col2 = t1.col2
      AND datum != t2.minDate
INNER JOIN ( 
             SELECT `pid`, max(datum) as maxxed
             FROM tablename 
             GROUP BY `pid`  
) t3 
ON            t1.pid = t3.pid 
        AND datum != maxxed

Ik heb er een select van gemaakt. Maar een delete werkt natuurlijk net zo goed ....

[Guillome]

Delete met innerjoin?

[NMe]

Guillome schreef op vrijdag 24 april 2009 @ 11:28:
Delete met innerjoin?

http://dev.mysql.com/doc/refman/5.1/en/delete.html

Maar goed, dit lijkt me juist iets wat je beter in zijn geheel kunt gaan voorkomen. Het lijkt me nou niet bepaald rocket science om te voorkomen dat dit soort dubbele data in je database terecht komt, en je ziet wat je anders voor maffe queries krijgt.

[LuCarD]

Guillome schreef op vrijdag 24 april 2009 @ 11:28:
Delete met innerjoin?

Waarom niet?

SQL:

delete t1
  FROM tablename AS t1
  INNER JOIN (
        SELECT `pid` , `col1` , `col2` , max( datum ) AS maxDate, min( datum ) AS minDate
        FROM tablename
        GROUP BY `pid` , `col1` , `col2`
        )  t2 
ON         t1.pid = t2.pid
      AND t2.col1 = t1.col1
      AND t2.col2 = t1.col2
      AND datum != t2.minDate
INNER JOIN ( 
             SELECT `pid`, max(datum) as maxxed
             FROM tablename 
             GROUP BY `pid`  
) t3 
ON            t1.pid = t3.pid 
        AND datum != maxxed

[ACM]

LuCarD schreef op vrijdag 24 april 2009 @ 11:07:
Ik heb er een select van gemaakt. Maar een delete werkt natuurlijk net zo goed ....

Met 40 miljoen records ben je waarschijnlijk beter af met een nieuwe tabel, daar de data in te stoppen (met een insert ... select bijv) en dan de oude te droppen/renamen en de nieuwe te gebruiken.

[LuCarD]

NMe schreef op vrijdag 24 april 2009 @ 11:34:
[...]

http://dev.mysql.com/doc/refman/5.1/en/delete.html

Maar goed, dit lijkt me juist iets wat je beter in zijn geheel kunt gaan voorkomen. Het lijkt me nou niet bepaald rocket science om te voorkomen dat dit soort dubbele data in je database terecht komt, en je ziet wat je anders voor maffe queries krijgt.

Soms kan je niet anders. Soms krijg je hele brakke data aangelevert.

ACM schreef op vrijdag 24 april 2009 @ 11:35:
[...]

Met 40 miljoen records ben je waarschijnlijk beter af met een nieuwe tabel, daar de data in te stoppen (met een insert ... select bijv) en dan de oude te droppen/renamen en de nieuwe te gebruiken.

Waarschijnlijk wel ... Plus je hebt voordeel dat je makkelijk terug als er een fout komt.

[ Voor 28% gewijzigd door LuCarD op 24-04-2009 11:40 ]

[Cartman!]

Dan zou ik zeggen dat je vanaf het moment dat je die verkeerde rijen eruit hebt gehaald wel gaat voorkomen dat je zulk soort data opslaat. Of is dat ook de bedoeling?

[LuCarD]

Cartman! schreef op vrijdag 24 april 2009 @ 11:39:
Dan zou ik zeggen dat je vanaf het moment dat je die verkeerde rijen eruit hebt gehaald wel gaat voorkomen dat je zulk soort data opslaat. Of is dat ook de bedoeling?

Ook daar heb je niet altijd controle over...

* LuCarD spreekt helaas uit ervaring

[jbdeiman]

@LuCarD
Mag ik vragen hoe het komt dat je daar ook niet altijd controle over hebt? Als je de data krijgt, kan je er toch ook voor zorgen dat die op een goede manier wordt verwerkt, tenminste dat is wel wat ik heb geleerd tijdens het werken aan een heel grote database met allerlei gegevens die ik aangeleverd krijg als CSV. Maar zelfs al zetten ze het op een brakke manier in de database, dan kan je nog die gegevens gebruiken en deze op een betere manier herstructureren met een scriptje wat wordt uitgevoerd na het opslaan in de database.

Natuurlijk kost dit wel wat tijd en moeite, maar het herstructureren scheelt in de toekomst ook ontzettend veel tijd en geld.

[NMe]

jbdeiman schreef op vrijdag 24 april 2009 @ 11:48:
@LuCarD
Mag ik vragen hoe het komt dat je daar ook niet altijd controle over hebt? Als je de data krijgt, kan je er toch ook voor zorgen dat die op een goede manier wordt verwerkt, tenminste dat is wel wat ik heb geleerd tijdens het werken aan een heel grote database met allerlei gegevens die ik aangeleverd krijg als CSV. Maar zelfs al zetten ze het op een brakke manier in de database, dan kan je nog die gegevens gebruiken en deze op een betere manier herstructureren met een scriptje wat wordt uitgevoerd na het opslaan in de database.

Natuurlijk kost dit wel wat tijd en moeite, maar het herstructureren scheelt in de toekomst ook ontzettend veel tijd en geld.

Controle hebben over de database is leuk maar als je geen controle hebt over de applicatie die de database gebruikt of als je daar van je baas geen tijd voor krijgt, dan kun je alsnog niks permanent verbeteren. En ik denk dat je LuCarD niet hoeft te vertellen wat al dan niet beter is in dit geval; hem kennende weet 'ie dat heel goed.

[jbdeiman]

Ooh, neej dat is het ook niet, ik begreep hem verkeerd. Ik maakte er meer van dat hij de data niet kon omzetten naar een beter werkbare manier van sorteren.

Waarvoor mijn excuus

[Zoijar]

Ook even je tabel locken oid. Als je een min/max datum selecteert in een subquery, dan kan er na die select een insert uit een ander proces tussen komen. Misschien is dat net dat record met de laatste datum die je moet bewaren, die dan niet gelijk is aan de geselecteerde max date, en dan dus helaas verloren gaat.

[Cartman!]

NMe schreef op vrijdag 24 april 2009 @ 11:52:
[...]

Controle hebben over de database is leuk maar als je geen controle hebt over de applicatie die de database gebruikt of als je daar van je baas geen tijd voor krijgt, dan kun je alsnog niks permanent verbeteren. En ik denk dat je LuCarD niet hoeft te vertellen wat al dan niet beter is in dit geval; hem kennende weet 'ie dat heel goed.

Maar dat weet de TS misschien niet natuurlijk Wellicht wil hij tussentijds opschonen oid, volgens mij is dat niet precies naar voren gekomen in dit topic.

[joppybt]

Ik zou dit niet krampachtig met een enkel SQL statement proberen op te lossen maar gewoon uitprogrammeren.
Rationale: doorloop tabel met een 'window' van drie records. De middelste mag telkens weg als de PiD gelijk is aan de eerste en de derde en (Col1, Col2) gelijk aan de eerste of de derde.

Iets als:

code:

DECLARE 2 setjes variabelen om het vorige record en 
    voor-vorige record te onthouden
FOR EACH record order by PID, Datum
    Als: A) huidige record en beide variabele-records dezelfde PID hebben
         B) En Col1/Col2 in vorige-record gelijk 
                    aan voor-vorige record of gelijk aan huidige record
    Dan: verwijderen vorige(!) record
    Anders: Kopieer vorige-record variabelen naar voor-vorige variabelen
    Altijd: Kopieer huidige record naar vorig-record variabelen
NEXT FOR

[NMe]

Hou wel in de gaten dat, tenzij je de hele tabel in het geheugen laadt, je daar een select query doet voor elk record in de tabel (waarbij je er wel 3 tegelijk ophaalt) en vervolgens ook nog een x aantal deletes. Uitgaande van wat de TS in zijn topicstart zei komt dat neer op zo'n 60 miljoen queries óf 20 miljoen queries en een hoog geheugengebruik. Afhankelijk van waar de software op draait en hoe bedrijfskritiek deze is, moet er heel wat tijd voor dat script uitgetrokken worden terwijl het op low prio draait.

[Guillome]

Eh je, een beetje DB tool doet dat in een paar seconden

[joppybt]

NMe schreef op vrijdag 24 april 2009 @ 16:13:
...komt dat neer op zo'n 60 miljoen queries óf 20 miljoen queries en een hoog geheugengebruik....

Dat is natuurlijk zeker niet de bedoeling. Mijn voorstel doet maar één query van alle records en doorloopt (in een verder niet aangegeven programmeertaal) de resulterende recordset daarvan gewoon sequentieel.

Elk record wordt maar één keer uit de database gelezen, daar zorgen de variabelen juist voor. Als je dit geheel dan ook nog in een stored procedure doet vermoed ik dat het efficienter is dan de eerder genoemde voorstellen met een enkel statement (die waarschijnlijk wél records meerdere keren lezen).

Voordeel van mijn oplossing is ook nog dat je bijvoorbeeld om de 1000 records een commit kunt doen. In dit specifieke geval levert dat geen inconsistentie op en houd je transactie veel kleiner. Oracle zou dat heel prettig vinden bijvoorbeeld.

[Verwijderd]

Dank voor alle gedachten, tips en opties. Ik begrijp dat het aantal records, naast de juiste programmeer-oplossing, misschien nog meer een issue is. Daarom zal ik nog wat gegevens m.b.t. de omgeving en vragen in jullie reacties opsommen, wellicht roept iemand op basis hiervan ook nog 'HO!' of 'GO!'

- ik heb in principe alle toegang en mogelijkheden w.b. scripts, server, applicaties en DB
- Linux 2.6.18 enterprise; Apache/1.3.37 (Unix) PHP/5.2.3 / 8GB ram / Supermicro Xeon quadcore 2.5
- de tabel betreft een InnoDB (REFER PID ON DELETE CASCADE) en is nu 4GB
- andere processen lopen tussen 0:00 u en 8:00 u, overdag is er behoudens selects geen insert/update-activiteit op deze tabel.

Cartman! schreef op vrijdag 24 april 2009 @ 11:39:
Dan zou ik zeggen dat je vanaf het moment dat je die verkeerde rijen eruit hebt gehaald wel gaat voorkomen dat je zulk soort data opslaat. Of is dat ook de bedoeling?

ja! op zich zijn het zijn niet echt 'verkeerde rijen', niet als het aantal pid's kleiner zou zijn. JA, gezien de omvang v/d DB is echter wel de bedoeling om een andere tactiek te kiezen voor deze tabel, al zal het altijd een fors aantal records blijven. Er zijn ca 2 miljoen pid's welke in de toekomst max 8 gekoppelde rijen binnen deze tabel mag hebben. Daar ben ik ook over aan het brainstormen. Het gaat nu om een oncontroleerbare hoeveelheid rijen die a) teruggebracht moet worden in aantal en b) zoals ik in de eerste post al aangaf: '...alvorens ik een andere tactiek moet bedenken om in de toekomst het aantal onnodige records in deze tabel te verminderen..'.

Deze tabel is 4GB op een totale omvang van 6,5 GB. Moge duidelijk zijn dat er bij deze tabel een inschattingsfoutje is gemaakt in een eerder stadium. echter is de data wel relevant en mag deze perse niet verloren gaan maar dient deze wel met minimaal 50% opgeschoond te worden door alleen de verschillen te bewaren en de gelijken te verwijderen.

Even voor de duidelijkheid, een juiste query in de vorm als die van LuCarD gaat het qua omvang van de tabel niet redden?

[ACM]

Guillome schreef op vrijdag 24 april 2009 @ 16:28:
Eh je, een beetje DB tool doet dat in een paar seconden

Als je reageert op NMe's opmerking over 60 miljoen queries... dan heb je toch echt wat meer nodig dan weer een van je one-liners. Als ik namelijk voor het gemak "een paar seconden" als 10 interpreteer, dan zou dat alsnog 6 miljoen queries per seconde zijn... er zijn maar weinig databasesystemen die zoveel readqueries halen, laat staan een mix van read- en writequeries.

joppybt schreef op vrijdag 24 april 2009 @ 17:01:
Elk record wordt maar één keer uit de database gelezen, daar zorgen de variabelen juist voor. Als je dit geheel dan ook nog in een stored procedure doet vermoed ik dat het efficienter is dan de eerder genoemde voorstellen met een enkel statement (die waarschijnlijk wél records meerdere keren lezen).

Voordeel van mijn oplossing is ook nog dat je bijvoorbeeld om de 1000 records een commit kunt doen. In dit specifieke geval levert dat geen inconsistentie op en houd je transactie veel kleiner. Oracle zou dat heel prettig vinden bijvoorbeeld.

Het blijven een heleboel losse operaties, waar niet zomaar een truckendoos als "de indexen uitschakelen" bij gebruikt kan worden. De enkele, grote select kan wel met dat soort trucken wat efficienter zijn schrijfwerk doen, voorop gesteld natuurlijk dat je niet al dat voordeel weer verliest doordat de grote query an sich veel trager is dan de losse kleintjes.
Mijn ervaring met mysql is overigens dat het toch meestal wel efficienter is om het allemaal in een grote query te stoppen, domweg omdat je dan veel minder roundtrips hebt. Zodra je het in een stored procedure weet te verwerken zal het allicht wat minder overhead hebben.
Desalniettemin is het opnieuw schrijven van een tabel al gauw sneller dan vele kleine stukjes uit de brontabel verwijderen.

Verwijderd schreef op vrijdag 24 april 2009 @ 19:14:
- andere processen lopen tussen 0:00 u en 8:00 u, overdag is er behoudens selects geen insert/update-activiteit op deze tabel.

Nog een reden voor de select into een nieuwe tabel en niet een grote delete, dan blokkeer je je lezers een stuk minder.

Even voor de duidelijkheid, een juiste query in de vorm als die van LuCarD gaat het qua omvang van de tabel niet redden?

Waarom niet? Daar zijn temporary (disk)tables voor. Het kan uiteraard wel nuttig zijn om de boel in batches te verwerken, in jouw geval is er niet echt een reden om het allemaal tegelijk te doen, maar kan je het ook in (mits er een index op staat) op een serie pid-ranges van elk 10.000-100.000 groot ofzo doen.

[ Voor 16% gewijzigd door ACM op 24-04-2009 19:36 ]

[Verwijderd]

code:

SELECT t1.*
  FROM tablename AS t1
  INNER JOIN (
        SELECT `pid` , `col1` , `col2` , max( datum ) AS maxDate, min( datum ) AS minDate
        FROM tablename
        GROUP BY `pid` , `col1` , `col2`
        )  t2 
ON         t1.pid = t2.pid
      AND t2.col1 = t1.col1
      AND t2.col2 = t1.col2
      AND datum != t2.minDate
INNER JOIN ( 
             SELECT `pid`, max(datum) as maxxed
             FROM tablename 
             GROUP BY `pid`  
) t3 
ON            t1.pid = t3.pid 
        AND datum != maxxed

bovenstaande werkwijze van LuCasD, evt aangepast naar een range-batch zoals ACM zinvol aangeeft, voorziet wel in de eerste/laatste datum per PID, maar vergelijkt alle col1+col2's met elkaar, terwijl de bedoeling is dat er alleen vergeleken wordt met de datum die er direct voor ligt.

Het kan dus voorkomen dat beide columns (col1 en 2) hetzelfde zijn dan een andere rij, maar omdat deze bv. niet aansluitend is -qua datum-, wordt de rij niet gemarkeerd als 'gelijk' en dient dus bewaard te blijven. het is dan op-neer-op (25-10-25) waarbij 'op' (25) niet hetzelfde is als 'op' (25) omdat er een 'neer' (10) tussenzit.

Bovenstaande code zorgt voor:

Id	PiD	Col1	Col2	Datum
10	1004	84	107	2009-03-01	<- blijft bewaard
201	1004	107	107	2009-02-18	<- blijft bewaard
270	1004	84	107	2009-02-14	<- wordt verwijderd, moet bewaard blijven, is anders dan 2009-02-10 PID 1004 (ID 340)
340	1004	107	107	2009-02-10	<- wordt verwijderd
380	1004	107	107	2009-02-06	<- wordt verwijderd, moet bewaard blijven, is anders dan 2009-02-01 PID 1004 (ID 431)
431	1004	107	196	2009-02-01	<- blijft bewaard

Wat het zou moeten zijn:

Id	PiD	Col1	Col2	Datum
10	1004	84	107	2009-03-01	<- bewaren, laatste datum met PID 1004
201	1004	107	107	2009-02-18	<- bewaren, col1 en 2 zijn weliswaar gelijk aan ID 340 dat is niet aansluitende rij waarmee vergeleken moet worden. dat is rij': 2009-02-14 PID 1004 (ID 270)
270	1004	84	107	2009-02-14	<- bewaren, col1 is gewijzigd t.o.v. 2009-02-10 PID 1004 (ID 340)
340	1004	107	107	2009-02-10	<- verwijderen, col1 en col2 zijn gelijk aan 2009-02-06 PID 1004 (ID 380)
380	1004	107	107	2009-02-06	<- bewaren, col2 is gewijzigd t.o.v. 2009-02-01 PID 1004 (ID 431)
431	1004	107	196	2009-02-01	<- bewaren, eerste datum met PID 1004

De reden dat ik hier ben gekomen met deze vraag is niet omdat ik weet hoe het moet en de bijbehorende code mis. de reden is dat ik een manier zoek om dit voor elkaar te krijgen, terwijl ik me ervan bewust ben dat het a) erg ingewikkeld is op 40 miljoen rijen en b) een hele andere denkwijze misschien beter is/kan zijn. Ik leg dus vooral uit wat het doel moet zijn, laat iedereen zich vrij voelen om te zeggen: 'je denkt verkeerd.. waarom bekijk je het niet eens van een andere kant..' of 'je denkt nu buiten de grenzen van het mogelijke, give it up'

ik probeer het ook nog even zo:
Per PID zou er dus een lijst van alle rijen op volgorde van datum (nieuw naar oud) geselecteerd moeten worden, waarin vanuit de nieuwste datum elke rij alleen met die eronder vergeleken wordt. behalve.. de oudste rij (er is in het geval van de oudste ook geen rij om mee te vergelijken). het bewaren van de nieuwste datum mag vervallen in de aanpak, dit blijkt helemaal niet zinvol, sorry.

en excuze moi ook voor de lange posts, ik zoek een goede manier om het goed over te brengen van hoofd naar letters

[ Voor 0% gewijzigd door Verwijderd op 25-04-2009 01:39 . Reden: door joppybt aangestipte fout rechtgezet :) ]

[joppybt]

Volgens mij is je laatste voorbeeld fout. Id 380 moet niet weg want zijn col2 (170) is anders dan de col2 van Id 431 (196). In genoemde voorbeeld moet alleen Id 340 verwijderd worden.

Verwijderd schreef op vrijdag 24 april 2009 @ 22:06:
ik me ervan bewust ben dat het a) erg ingewikkeld is op 40 miljoen rijen en b) een hele andere denkwijze misschien beter is/kan zijn.

Veel records maakt een probleem niet ingewikkeld, het zorgt er alleen voor dat een inefficiente oplossing misschien lang duurt of (te) veel geheugen vergt.

Je probleem heeft twee aspecten:

1. Hoe vind ik de juiste te verwijderen (of te houden records)
   
   Dit is op te te lossen met een (naar ik vermoed onwerkbaar) complex SQL-statement of met mijn voorgestelde procedurele oplossing
2. Hoe verwijder ik die efficient.
   
   Dit is op te lossen door de foute records telkens te DELETEn of door de goede records te inserten in een tijdelijke nieuwe tabel

Voor de oplossing van probleem 2 is het vooral belangrijk hoeveel records verwijderd gaan worden. Is dat meer dan de een bepaald percentage dan ben je waarschijnlijk sneller klaar met een nieuwe tabel aanmaken. Een en ander hangt dan weer af van hoeveel indexen bijgewerkt moeten worden bijvoorbeeld.

offtopic:
Klopt het dat het hier gaat om een tabel met meetwaarden per proces (PID) op verschillende datums waarbij opvolgende identieke meetwaren samengenomen moeten worden?

[Verwijderd]

joppybt schreef op zaterdag 25 april 2009 @ 00:20:
Volgens mij is je laatste voorbeeld fout. Id 380 moet niet weg want zijn col2 (170) is anders dan de col2 van Id 431 (196). In genoemde voorbeeld moet alleen Id 340 verwijderd worden.

yep, you're right. heb het rechtgezet

joppybt schreef op zaterdag 25 april 2009 @ 00:20:
Voor de oplossing van probleem 2 is het vooral belangrijk hoeveel records verwijderd gaan worden. Is dat meer dan de een bepaald percentage dan ben je waarschijnlijk sneller klaar met een nieuwe tabel aanmaken.

de schatting op dit moment is dat dat ronde de helft zal zijn. 40 a 60 %. dit is echter en helaas per steekproef met een nog niet geheel correcte select-query en op basis van slechts 0.25 % getest. Voorlopig kan ik dus net niet zeggen of er meer overblijft of weg gaat. laten we het houden op fifty/fifty. ik heb een testtabel gemaakt met de laatste 100.000 rijen en ga vannacht weer door met puzzelen.

tips, ideeen en opmerkingen mogen blijven komen, ik heb er echt wat aan!

offtopic:
jep

[Verwijderd]

Deze query ($sql2) geeft zowaar en redelijk snel de juiste records die verwijderd moeten worden, voor zover getest zijn alle resultaten zoals gewenst.. De werking ervan staat overigens los van het feit of deze manier zinnig is om te gebruiken, but it is nice to have all options

Als ik deze select-query om wil zetten naar een delete-query krijg ik een error. maar ik ben nogal moe, dus ik ga eerst nog even een paar uur slapen als iemand vast ziet waarom de omzetting naar een delete voor problemen zou zorgen of een andere optimalisatie ziet van de query an-sich.. shoot!

oja.. is het nog nuttig om een sleep() in de while te gooien of is het in stukken hakken van de operatie via een PID-range genoeg en kost een sleep() teveel tijd op zo'n aantal PID's?

PHP:

$sql = mysql_query("
        SELECT DISTINCT (PID) 
        FROM tblname 
        WHERE (`PID` BETWEEN xxxxx AND xxxxx)
        ");

    while ($row = mysql_fetch_array($sql)) 
    {

    $sql2 = mysql_query("
            SELECT t1.* FROM tblname t1 
            WHERE t1.PID = '".$row['PID']."' AND EXISTS (
                SELECT t2.* FROM tblname t2 
                WHERE t1.PID = t2.PID 
                AND t1.COL1 = t2.COL1 
                AND t1.COL2 = t2.COL2 
                AND t2.DATE = (
                    SELECT t3.DATE FROM tblname t3 
                    WHERE t3.DATE < t1.DATE 
                    AND t3.PID = t1.PID 
                    ORDER BY t3.DATE DESC LIMIT 0 , 1) 
                LIMIT 0 , 1) 
            ORDER BY t1.DATE DESC
            ");

    }

[NMe]

Je krijgt "een error"? Wauw, daar kunnen we veel mee.

Ik gok dat het te maken heeft met die LIMIT in je subqueries. Volgens de MySQL manual kan dat helemaal niet werken.

[Verwijderd]

we hebben een E.. we hebben een R.. we hebben een R we hebben een O en we hebben een R..

sorry, was moe en wilde nog even die query posten maar had de error zo snel even niet meer bij de hand, vandaar dat ik schreef dat ik eerst ging slapen en 'mocht iemand vast zien wat het zou kunnen zijn..'.

je gok is gezien de error vermoedelijk een juiste, thanx.
is er van deze query dan wel een delete-versie te maken?
tenslotte heeft ie de limit in de subquery toch nodig om de juiste vergelijking te kunnen maken.. ?
of moet ik dan de id's uit deze query in een array gooien en in een nieuwe query die id's deleten.. ?

op maar weer naar de manual..
gedachten blijven welkom!

[ Voor 10% gewijzigd door Verwijderd op 25-04-2009 18:18 ]