Snelste manier om te vergelijken in MySQL?

[KK]

Dit is de situatie:

Een prijslijst in CSV formaat met bijna 40.000 artikelen wordt via een PHP script gedownload en in een database geplaatst op verschillende tijdstippen (met cron job), en er moet ook telkens gekeken worden of er geen nieuwe artikelen bijgekomen zijn. Indien er nieuwe artikelen zijn, dan wordt daarvan een lijstje naar mij doorgemaild. Dit werkt allemaal wel, maar het hele proces duurt wel gemakkelijk een kwartier.

Momenteel doe ik het als volgt, en ik denk dat het verre van ideaal is:
Eerst wordt de CSV file gedownload
Daarna ga ik elke regel van de CSV file af en doe op de inhoud van die regel een search in de database (die reeds opgevuld was van de vorige keer dat heel dit proces doorlopen is). Op die manier kom ik dus te weten of er sinds de vorige update nieuwe artikelen bijgekomen zijn. Maar dit betekent wel dat er bijna 40.000 SQL queries uitgevoerd worden na elkaar, eentje voor elk artikel uit de CSV file.
Als dat allemaal gedaan is wordt de database eerst geledigd en daarna terug opgevuld met de inhoud van de nieuwe CSV file.

Ik wil dus eigenlijk weten of er geen snellere en vooral efficiëntere manier is om data uit een CSV file te vergelijken met data in een database. Dus enkel checken op nieuwe data, niet op gewijzigde data zoals prijswijzigingen e.d.

Enige ideeën?

[meon]

Kan je de keys en de values waar je op zoekt niet eerst kopieren naar een MEMORY/HEAP-tabel en daar je operaties op loslaten? Het temporary-table-principe ...
Dat type tabel is retesnel en kan je makkelijk met vergelijkbare code 10x tijdswinst boeken.

[KK]

Weer wat bijgeleerd blijkbaar, ik had geen idee dat er zoiets bestond als temp tables
Ik heb het nu even getest, een temp tabel aangemaakt met daarin een kopie van enkel alle artikelcodes uit de originele tabel (dat is het enige wat ik nodig heb), en het updaten duurt nu maar half zo lang meer

Bedankt voor de tip meon!

[cloink]

Ik ga even the obvious staten: maar je legt toch indexen op uw keys hé?

[Astralon]

Weer wat bijgeleerd blijkbaar, ik had geen idee dat er zoiets bestond als temp tables
Ik heb het nu even getest, een temp tabel aangemaakt met daarin een kopie van enkel alle artikelcodes uit de originele tabel (dat is het enige wat ik nodig heb), en het updaten duurt nu maar half zo lang meer

Bedankt voor de tip meon!

Hm, ik ben geen MySQL specialist maar ik doe regelmatig gelijkaardige dingen in MS SQL Server en 40000 records worden zeker in minder dan een minuut verwerkt. Ik kan me niet voorstellen dat dit een MySQL probleem is.

Dus zoals cloink al schreef heb je de juiste indexen op je tabel staan?
Waarom lees je niet de volledige spreadsheet in een tijdelijke tabel waarna je kan vergelijken en updaten uit de tijdelijke tabel?

[crapiecorn]

Hoe update de csv ? Komt nieuwe data er gewoon achteraan bij, of kan een nieuwe csv een totaal andere stuctuur hebben ?

[KK]

Er was inderdaad ook geen index gedefinieerd
Ondertussen heb ik het al kunnen terugbrengen naar 7 minuten, terwijl het daarvoor 15 tot 20 minuten duurde. Voor mij is dit goed genoeg, aangezien het toch automatisch gebeurt met een cron job en ik er dus niet echt op moet zitten wachten

In ieder geval bedankt voor de hulp!

[crapiecorn]

Een slechte programmeer techniek "sneller" maken lijkt me niet de beste oplossing.

[ubremoved_539]

Momenteel doe ik het als volgt, en ik denk dat het verre van ideaal is:
Eerst wordt de CSV file gedownload

Waarom doe je dit op CSV niveau... je hebt net een database voor dergelijke dingen.

Doe deze controles gewoon in je SQL database !

[pennywise]

kan je niet met een query eerst onderzoeken welke productnummers nieuw zijn, en ze dan gewoon met een toevoegquery bij de originele database bijvoegen ?

vermits het hier om unieke nummers volgens mij gaat, zou je er ook een primaire key van maken denk ik

[crapiecorn]

Momenteel doe ik het als volgt, en ik denk dat het verre van ideaal is:
Eerst wordt de CSV file gedownload

Waarom doe je dit op CSV niveau... je hebt net een database voor dergelijke dingen.

Doe deze controles gewoon in je SQL database !

csv doet misschien dienst als een soort webservice.

[KK]

csv doet misschien dienst als een soort webservice.

De CSV komt inderdaad van een externe bron (FTP van leverancier) en wordt via file in een array geplaatst.

kan je niet met een query eerst onderzoeken welke productnummers nieuw zijn, en ze dan gewoon met een toevoegquery bij de originele database bijvoegen ?

vermits het hier om unieke nummers volgens mij gaat, zou je er ook een primaire key van maken denk ik

Een primary key maken van de artikelcodes ging niet, omdat er blijkbaar enkele duplicates in zitten. Maar kan je mij eens uitleggen hoe je met 1 query de nieuwe artikelen eruit haalt? Mijn kennis van SQL is helaas maar vrij basic

[Astralon]

Iets als...

Code: Selecteer alles

CREATE TEMPORARY TABLE TempTable ( ID int, Name char(100) ) TYPE=HEAP; 
LOAD DATA INFILE 'path/file.txt' INTO TABLE TempTable;
SELECT * FROM TempTable WHERE ID NOT IN (SELECT ID FROM MyTable);
DROP TABLE TempTable; 

Primary key op meer dan 2 velden zetten zodat je iets uniek krijgt?

[meon]

Merk trouwens op dat MySQL ook het CSV-storage engine kent (moet wel meegecompileerd zijn). Dan kan je gewoon een CSV-file als tabel gebruiken.

.. in theorie, ik heb het nog nooit gedaan, maar misschien valt er hier wat mee te doen

[KK]

Iets als...

Code: Selecteer alles

SELECT * FROM TempTable WHERE ID NOT IN (SELECT ID FROM MyTable);

Dat is wat ik zocht, het gaat nu nog een pak sneller (net geen 3 minuten), maar vooral: slechts 1 query i.p.v. bijna 40.000

Nogmaals bedankt!