Filtrare 5.000.000 di indirizzi email da db

[evil0x]

Salve,
Dovrei leggere 5.000.000 di indirizzi email da database mysql e confrontarne ognuno con le righe all'interno di una tabella contenente delle keywords, se un indirizzo email contiene una delle keyword lo script in php dovrà inserire quell'indirizzo in un CSV chiamato keyword.csv

Esempio:

Keyword -> paolo
Keyword -> 33
Keyword-> lasagne
Email -> paolo33z@asdasdasd.it

L'indirizzo paolo33z@asdasdasd.it dovrà finire in paolo.csv 33.csv ma non in lasagne.csv.

Data l'enormità dei dati di partenza (5 milioni) chiedo un pò di brainstorming su come approcciare il problema.

Grazie!
Saluti (almeno 5 milioni di saluti

[Santino83_02]

quante volte dovrai fare questo lavoro? No perchè un conto farlo una volta sola, un conto farlo ripetutamente

nel secondo caso sposterei l'attenzione nel programmare direttamente il database per farti restituire i dati filtrati per keywords, usando indici, views, tabelle temporanee etc

nel primo caso potresti accontentarti di fare un brute force e ciclare sui milioni di record di risultati ottenuti.

di sicuro hai da aumentare parecchio i temi di timeout per lo script php

[daniele_dll]

o non usi php o lo usi solo marginalmente per fare questo lavoro e deleghi le operazioni principali al database altrimenti le associazioni per gli animali ti faranno causa per sfruttamento di criceti da server

[Dascos]

Cicli sulle keywords ed estrai in "like" dalle email.
Per fare in modo da non gravare sul server puoi crearti uno script che "cronnizzi", salvando di volta in volta l'ultimo intervallo estratto.
Spiego.
POC

funzione crea csv {

limite = select primo, ultimo from tabella_limiti;
if !limite {
   limite_inf = 0
   limite sup = 5000
} else {
   limite_inf = primo
   limite_sup = ultimo
}

chiavi = select keyword from tabella_chiavi
foreach chiavi as chiave {
   emails = selet email from tabella_email where email like '%chiave%' limit limite_inf, limite_sup
   email_formato_csv = elabora_email(emails)
   file put chiave.csv => email_formato_csv
}

aggiorna_limit = update tabella_limiti set primo = limite_sup, ultimo = ultimo+limite_sup
}

Questo script lo metti in un crontab e lo fai eseguire ogni...boh...5 minuti?
5.000.000 / 5.000 = 1.000 "cicli" * 5 minuti = 5.000 minuti / 60 = 83 ore = 3 giorni circa.
Se va eguito più frequentemente adatti il cron e ottimizzi il codice, eventualmente

[daniele_dll]

Originariamente inviato da Dascos
Cicli sulle keywords ed estrai in "like" dalle email.
Per fare in modo da non gravare sul server puoi crearti uno script che "cronnizzi", salvando di volta in volta l'ultimo intervallo estratto.
Spiego.
POC

funzione crea csv {

limite = select primo, ultimo from tabella_limiti;
if !limite {
   limite_inf = 0
   limite sup = 5000
} else {
   limite_inf = primo
   limite_sup = ultimo
}

chiavi = select keyword from tabella_chiavi
foreach chiavi as chiave {
   emails = selet email from tabella_email where email like '%chiave%' limit limite_inf, limite_sup
   email_formato_csv = elabora_email(emails)
   file put chiave.csv => email_formato_csv
}

aggiorna_limit = update tabella_limiti set primo = limite_sup, ultimo = ultimo+limite_sup
}

Questo script lo metti in un crontab e lo fai eseguire ogni...boh...5 minuti?
5.000.000 / 5.000 = 1.000 "cicli" * 5 minuti = 5.000 minuti / 60 = 83 ore = 3 giorni circa.
Se va eguito più frequentemente adatti il cron e ottimizzi il codice, eventualmente

La cosa migliore da fare, è delegare l'operazione ad una (complessa) query che indubbiamente impiegherà un tempo decisamente inferiore

[Dascos]

Si possono fare delle inner join, evenualmente, ma non credo molto di più e si rischia di rendere l'elaborazione più complessa di quel che serve.
Una querettona complessa potrebbe anche peggiorare le cose...una like sembra comunque da fare, non credo sia il caso di appesantire ulteriormente la ricerca. Anche su 5 milioni di record, se gli indici sono ben fatti, estrarre 5000 record "secchi" non è impegnativo e ciclare 5000 * n volte, dato che l'elaborazione per scrivere un csv non è niente di complicatissimo, idem.

Ho provato su 3,5 milioni di record

Stato 	Tempo
starting 	0.000023
checking query cache for query 	0.000068
Opening tables 	0.000021
System lock 	0.000009
Table lock 	0.000045
init 	0.000055
optimizing 	0.000016
statistics 	0.000021
preparing 	0.000019
executing 	0.000007
Sending data 	0.018813
end 	0.000020
query end 	0.000008
freeing items 	0.000048
storing result in query cache 	0.000013
logging slow query 	0.000007
cleaning up 	0.000008

Questa è la struttura della tabella

CREATE TABLE IF NOT EXISTS `spider_logs` (
  `sid` int(10) NOT NULL AUTO_INCREMENT,
  `bot` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `query_string` text COLLATE latin1_general_ci NOT NULL,
  `entry_date` int(10) NOT NULL DEFAULT '0',
  `ip_address` varchar(32) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`sid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3495510 ;

e la query

SELECT *
FROM `spider_logs`
WHERE `query_string` LIKE '%topic%'

Il carico macchina al momento dell'esecuzione della query è 7 (lo dico perchè a carico minore forse ci mette meno)

Comunque, di prim'acchitto quella che ho proposto mi pare la soluzione più semplice, date le specifiche dell'utente. Vediamo cosa ne salta fuori dagli altri

[daniele_dll]

quello che sto dicendo è che se ha una tabella con le parole chiavi, tecnicamente potrebbe fare una query tipo

SELECT
  t1.*, t2.*
FROM
  tabella1 AS t1
  INNER JOIN tabella2 AS t2 ON
  (
    t1.email LIKE CONCAT('%', t2.parolachiave, '%')
  )

Oppure al posto di like si possono usare le espressioni regolari

[Dascos]

Hmmm...ma questa query, per quanto mi sembri corretta, rischia di tirarti fuori un botto di record (potenzialmente tutti i 5 milioni e forse anche di più) e si rischia appunto di appesantire notevolmente sia il db che l'elaborazione php, col risultato di andare in timeout di elaborazione...
Per quanto estrarre 5 milioni di record sia relativamente rapido, non lo è altrettanto farne l'elaborazione

estrarre il record,
controllare in che file andrebbe scritto,
aprire il file,
controllare se è già stata scritto,
scrivere nel file,
chiudere il file,

il tutto ripetuto potenzialmente 5 milioni di volte (e magari anche più, dato che una mail può avere più di una corrispondenza con parola chiave!).

[oly1982]

quando commentato santino e daniele_dll sono un pò intimorito nel rispondere... cmq dico la mia.

Se si tratta di una procedura "una tantum", cioè la faccio una volta e poi magari la rifaccio fra 6 mesi, io sceglierei una procedura basata su mysql o php che mi dia la maggiore garanzia di estrazione dei dati corretti... senza starmi troppo a preoccupare delle prestazioni.

Discorso radicalmente diverso se si tratta di una procedura di routine...


------------------------
[EPISODIO OFF TOPIC]
quando iniziai a smanettare con php mysql feci una volta uno scriptino che salvava le date in formato errato in un campo varchair e vi erano più di 5000 record. Ebbi l'esigenza di modificarli (ovvio...) e ricordo che feci uno script "una tantum" che faceva il SELECT di tutti i record e il successivo UPDATE in un apposito campo creato con ALTER TABLE di tipo DATETIME... verificai che era andato bene ed eliminai il campo formattato in modo erroneo.

Ovviamente feci un dump del db e lo feci in locale... ci mise 6-7 secondi per eseguire il tutto... ma andò tutto liscio.
[/EPISODIO OFF TOPIC]

[Dascos]

Originariamente inviato da oly1982
------------------------
[EPISODIO OFF TOPIC]
quando iniziai a smanettare con php mysql feci una volta uno scriptino che salvava le date in formato errato in un campo varchair e vi erano più di 5000 record. Ebbi l'esigenza di modificarli (ovvio...) e ricordo che feci uno script "una tantum" che faceva il SELECT di tutti i record e il successivo UPDATE in un apposito campo creato con ALTER TABLE di tipo DATETIME... verificai che era andato bene ed eliminai il campo formattato in modo erroneo.

Ovviamente feci un dump del db e lo feci in locale... ci mise 6-7 secondi per eseguire il tutto... ma andò tutto liscio.
[/EPISODIO OFF TOPIC]

6 o 7 secondi per 5000 record...moltiplica per 1000 e hai 6000 secondi...diciamo che magari un server ci mette 1 secondo...moltiplica per 1000 e sei comunque fuori dal timeout di php, che è 60 o 120 secondi...